如何在SQL中获取连接值的计数 [英] How to get count of concatenated value in SQL

问题描述

我想连接4个来自3个不同表的列并获得连接数。

表1：

 ID名称国家
 1 Rick Jpn 
 2 Ken Chn 
 3 Tim Swz 
 4 Rick Jpn 
 5 Ken Chn <  

表2：

 ID Curr Amt 
 1日元344.55 
 2 Yun 225 
 3 Frn 459 
 4日元344.55 
 5 Yun 225 
 

结果：

 ID Name Country Curr Amt连续值数
 1 Rick Jpn Yen 344.55 RickJpnYen344.55 2 
 2 Ken Chn Yun 225 KenChnYun25 2 
 3 Tim Swz Frn 459 TimSwzFrn459 1 

我正在寻找连接列并计算连接值列。

解决方案

这里有很多问题：

1）什么是ird table？

2）你怎么把桌子捆在一起？



简要介绍一下你展示的两张桌子，以及输出我不认为你已经完全理解关系数据库是什么了：表和它们包含的数据之间的关系。

我很想改变表格：

 Id名称CountryID 
 1 Rick 1 
 2 Ken 2 
 3 Tim 3 

 Id国家货币
 1 Jpn日元
 2 Chn Yun 
 3 Swz Frn 

< pre lang =text> Id UserId Amt
1 1 344.55
2 2 225
3 3 459
4 1 344.55
5 2 225

然后弄清楚你想要做什么变得相当简单。

试试这个..

查询

 选择 distinct a.Name  as 名称，a.Country  as 国家/地区，b.Curr  as  Curr，b.Amt  as  Amt，（cast（a.Name  as  varchar（ 50 ））+ cast（a.Country  as  varchar（ 50 ））+ cast（b.Curr < span class =code-keyword> as  varchar（ 50 ））+ cast（b.Amt  as  varchar（ 50 ））） as  joinColumn，count（Name） as  CountValue 
 来自表_1，表_2 b 其中​​ a.Id = b.Id  group  by a.Name，a.Country，b.Curr，b.Amt 

I want to concatenate 4 columns which are from 3 different tables and get the count of concatenate.
Table1:

ID Name Country
  1 Rick Jpn
  2 Ken  Chn
  3 Tim  Swz
  4 Rick Jpn
  5 Ken  Chn<

Table2:

ID Curr Amt
 1 Yen 344.55
 2 Yun 225
 3 Frn 459
 4 Yen 344.55
 5 Yun 225

Result:

ID Name Country Curr Amt     Concatenated value Count
  1 Rick Jpn     Yen  344.55  RickJpnYen344.55     2
  2 Ken  Chn     Yun  225     KenChnYun25          2
  3 Tim  Swz     Frn  459     TimSwzFrn459         1

I am looking for concatenating the columns and count the concatenated value column.

解决方案

There are a number of problems here:
1) What is the third table?
2) how are you tying the tables together?

From a brief look at the two tables you do show, and the output I don't think you have quite understood what a relational database is all about: the relationships between tables and the data they contain.
I'd be tempted to change the tables:

Id   Name   CountryID
1    Rick   1
2    Ken    2
3    Tim    3

Id   Country Currency
1    Jpn     Yen
2    Chn     Yun
3    Swz     Frn

Id   UserId Amt
1    1      344.55 
2    2      225
3    3      459
4    1      344.55
5    2      225

And then it becomes fairly simple to work out what you want to do.

try this..
Query

select distinct a.Name as Name,a.Country as Country,b.Curr as Curr,b.Amt as Amt,(cast(a.Name as varchar(50))+cast(a.Country as varchar(50))+cast(b.Curr as varchar(50))+cast(b.Amt as varchar(50))) as joinColumn,count(Name) as CountValue
from Table_1 a,Table_2 b where a.Id=b.Id group by a.Name,a.Country,b.Curr,b.Amt

这篇关于如何在SQL中获取连接值的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！