数据库的SQLSERVER 中SQL问题
0
悬赏园豆：30
[待解决问题]
我想实现一个功能，表的字段如
name classid score1 score2 score3
A C1 90 0 0
A C1 99 0 0
A C2 0 90 0
A C2 0 100 0
B C1 90 0 0
D C3 0 0 99
D C3 0 0 100
实现的功能如：
name C1 C2 C3
A 2 190 0
B 1 0 0
D 0 0 199
就是说 根据name classid 进行分组 计算 C1 C2 C3的数量，C1 是不为零的个数，而C2 C3是总和。这个SQL语句如何实现？
谢谢大家啦。
所有回答(7)
0
select name, sum(case when classid='C1' and score1+score2+score3>0 then score1/isnull(score1/nullif(score1,0),1)+score2/isnull(score2/nullif(score2,0),1)+score3/isnull(score3/nullif(score3,0),1) else 0 end) as C1, sum(case when classid='C2' then score1+score2+score3 else 0 end) as C2, sum(case when classid='C3' then score1+score2+score3 else 0 end) as C3 from TABLE group by name
原来写错了，改了下
这个应该才是正解吧
0
楼上的看起来好复杂！！！我的解决方案如下： SELECT NAME,C1=(SELECT COUNT(*) FROM t1 WHERE score1!=0 AND NAME=t1.name),SUM(score2),SUM(score3) FROM t1 GROUP BY NAME
精辟啊
A 3 190 0
B 3 0 0
D 3 0 199
0
select name, (select COUNT(1) from scores where score1!=0 and name=t.name) as c1, SUM(score2) as c2,SUM(score3) as c3 from scores t group by name
0
select name,(select count(score1) from [tables] where score1!=0 )as c1,sum(score2),sum(score3),from [tables] t group by name
0
路过学习。
0
if object_id('tempdb..#a') is not null drop table #a go create table #a ( name varchar(20), Classid varchar(20), score1 int, score2 int, score3 int ) insert into #a select 'A' ,'C1' , 90 , 0 , 0 union all select 'A' ,'C1' , 99 , 0 , 0 union all select 'A','C2' , 0 , 90 , 0 union all select 'A' ,'C2' , 0 , 100 , 0 union all select 'B' ,'C1' , 90 , 0 , 0 union all select 'D' , 'C3' , 0 , 0 , 99 union all select 'D' ,'C3' , 0 , 0 , 100 ---- select name , (select isnull(sum(case when score1!=0 or score2!=0 or score3 !=0 then 1 else 0 end),0) from #a b where b.name=a.name and b.Classid='c1') as C1, SUM(score2),SUM(score3) from #a a group by name --*************************** A 2 190 0 B 1 0 0 D 0 0 199
0
declare @temp table ( name varchar(20), Classid varchar(20), score1 int, score2 int, score3 int ) insert into @temp select 'A' ,'C1',90,0,0 union all select 'A' ,'C1',99,0,0 union all select 'A','C2' ,0 ,90,0 union all select 'A' ,'C2' ,0, 100, 0 union all select 'B' ,'C1' ,90 ,0 ,0 union all select 'D' , 'C3', 0 , 0,99 union all select 'D' ,'C3' , 0,0 ,100 select * from @temp select name, sum(case when Classid='C1' and isnull(score1,0)+isnull(score2,0)+isnull(score3,0)<>0 then 1 else 0 end) as C1, C2=SUM(case when Classid='C2' then isnull(score1,0)+isnull(score2,0)+isnull(score3,0) else 0 end), C3=SUM(case when Classid='C3' then isnull(score1,0)+isnull(score2,0)+isnull(score3,0) else 0 end) from @temp group by name
