27,580
社区成员
发帖
与我相关
我的任务
分享求sql统计的语句，sql好久没用，忘完了
各位大哥，现在小弟想做一个统计数据，大概原始数据是这样 的
想统计的结果如下：
按照name、和type来统计，相同的name和type的count1和count2合计， 结果中的A列是积。
麻烦各位，
count1 count2 type name
10 1 A 11
20 2 B 11
30 3 A 11
40 4 B 11
50 5 A 22
60 6 B 33
想统计的结果如下：
name typeAcount1 typeAcount2 A typeBallcount typeBerrcount B
11 40 4 140 60 6 360
22 50 5 250 0 0 0
33 0 0 0 60 6 360
按照name、和type来统计，相同的name和type的count1和count2合计， 结果中的A列是积。
麻烦各位，
...全文
请发表友善的回复…
发表回复
reenjie 2014-12-03
- 打赏
- 举报
還可以調成這樣
select *,typeAcount1*typeAcount2 as A,typeBallcount*typeBerrcount as B from(
select name,
sum(case when [type]='A' then count1 else 0 end) as typeAcount1,
sum(case when [type]='A' then count2 else 0 end) as typeAcount2,
sum(case when [type]='B' then count1 else 0 end) as typeBallcount,
sum(case when [type]='B' then count2 else 0 end) as typeBerrcount
from tbl group by name) as a
order by name
kobaer 2014-12-03
- 打赏
- 举报
都是牛人。
问题解决了。
还有点小细节，我自己调整
谢谢各位
hepe00 2014-12-03
- 打赏
- 举报
----------------------------------------------------------------
select [name],
sum((case [type] when 'A' then count1 else 0 end)) as typeAcount1,
sum((case [type] when 'A' then count2 else 0 end )) as typeAcount2,
sum((case [type] when 'A' then count1 else 1 end )*count2) as A,
sum((case [type] when 'B' then count1 else 0 end)) as typeBcount1,
sum((case [type] when 'B' then count2 else 0 end )) as typeBcount2,
sum((case [type] when 'B' then count1 else 1 end )*count2) as B
from Tb
group by [name
]
还在加载中灬 2014-12-03
- 打赏
- 举报
SELECT
ISNULL(T1.name,T2.name)name
,ISNULL(T1.count1,0)typeAcount1
,ISNULL(T1.count2,0)typeAcount2
,ISNULL(T1.count1*T1.count2,0)A
,ISNULL(T2.count1,0)typeBallcount
,ISNULL(T2.count2,0)typeBerrcount
,ISNULL(T2.count1*T2.count2,0)B
FROM(SELECT name,SUM(count1)count1,SUM(count2)count2 FROM TB WHERE type='A' GROUP BY name)T1
FULL JOIN(SELECT name,SUM(count1)count1,SUM(count2)count2 FROM TB WHERE type='B' GROUP BY name)T2
ON T1.name=T2.name
ORDER BY ISNULL(T1.name,T2.name)reenjie 2014-12-03
- 打赏
- 举报
小調一下，別名搞錯了
create table tbl
(
count1 int,
count2 int,
[type] varchar(5),
name varchar(5)
)
insert tbl values(10,1,'A','11'),
(20,2,'B','11'),
(30,3,'A','11'),
(40,4,'B','11'),
(50,5,'A','22'),
(60,6,'B','33')
select name,
sum(case when [type]='A' then count1 else 0 end) as typeAcount1,
sum(case when [type]='A' then count2 else 0 end) as typeAcount2,
sum(case when [type]='A' then count1 else 0 end)*sum(case when [type]='A' then count2 else 0 end) as A,
sum(case when [type]='B' then count1 else 0 end) as typeBallcount,
sum(case when [type]='B' then count2 else 0 end) as typeBerrcount,
sum(case when [type]='B' then count1 else 0 end)*sum(case when [type]='B' then count2 else 0 end) as B
from tbl group by name
order by name
xiaodongni 2014-12-03
- 打赏
- 举报
select name,
isnull(sum(case type when 'a' then count1 end),0) as typeacount1,
isnull(sum(case type when 'a' then count2 end),0) as typeacount2,
isnull(sum(case type when 'a' then count1 end),0)*isnull(sum(case type when 'a' then count2 end),0) as a ,
isnull(sum(case type when 'b' then count1 end ) ,0)as typebcount1 ,
isnull(sum(case type when 'b' then count2 end) ,0)as typebcount2,
isnull(sum(case type when 'b' then count1 end ) ,0)*isnull(sum(case type when 'b' then count2 end) ,0) as b
from (select name,type,sum(count1) as count1,sum(count2) as count2
from test
group by name ,type ) as a
group by name
--结果
name typeacount1 typeacount2 a typebcount1 typebcount2 b
----------- ----------- ----------- ----------- ----------- ----------- -----------
11 40 4 160 60 6 360
22 50 5 250 0 0 0
33 0 0 0 60 6 360
警告: 聚合或其他 SET 操作消除了 Null 值。
(3 行受影响)
kobaer 2014-12-03
- 打赏
- 举报
谢谢， 你的写法是对的， 不知道效率咋样。 呵呵
reenjie 2014-12-03
- 打赏
- 举报
create table tbl
(
count1 int,
count2 int,
[type] varchar(5),
name varchar(5)
)
insert tbl values(10,1,'A','11'),
(20,2,'B','11'),
(30,3,'A','11'),
(40,4,'B','11'),
(50,5,'A','22'),
(60,6,'B','33')
select name,
sum(case when [type]='A' then count1 else 0 end) as typeAcount1,
sum(case when [type]='A' then count2 else 0 end) as typeAcount2,
sum(case when [type]='A' then count1 else 0 end)*sum(case when [type]='A' then count2 else 0 end) as A,
sum(case when [type]='B' then count1 else 0 end) as typeBcount1,
sum(case when [type]='B' then count2 else 0 end) as typeAcount2,
sum(case when [type]='B' then count1 else 0 end)*sum(case when [type]='B' then count2 else 0 end) as A
from tbl group by name
kobaer 2014-12-03
- 打赏
- 举报
不对，typebcount1,typebcount2没有，并且你类型是分开行统计的。
11 B 60 6 6 200
11 A 40 4 100 4
33 B 60 6 6 360
22 A 50 5 250 5
hepe00 2014-12-03
- 打赏
- 举报
----------------------------------------------------------------
select [name],[type],
sum(count1) as typeAcount1,sum(count2) as typeAcount2,
sum((case [type] when 'A' then count1 else 1 end )*count2) as A,
sum((case [type] when 'B' then count1 else 1 end )*count2) as B
from Tb
group by [name],[type]