求sql统计的话语，sql好久没用，忘完了
求sql统计的语句，sql好久没用，忘完了
想统计的结果如下：
按照name、和type来统计，相同的name和type的count1和count2合计， 结果中的A列是积。
麻烦各位，
------解决思路----------------------
------解决思路----------------------
试试这个
------解决思路----------------------
------解决思路----------------------
这样如何
本帖最后由 kobaer 于 2014-12-03 14:41:06 编辑
各位大哥，现在小弟想做一个统计数据，大概原始数据是这样 的
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列是积。
麻烦各位，
------解决思路----------------------
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
------解决思路----------------------
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 行受影响)
试试这个
------解决思路----------------------
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)
------解决思路----------------------
----------------------------------------------------------------
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
]
这样如何