首页新闻找找看学习计划

数据库的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语句如何实现?

  谢谢大家啦。

小菜丑呢的主页 小菜丑呢 | 初学一级 | 园豆:172
提问于:2013-06-29 14:52
< >
分享
所有回答(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 

原来写错了,改了下
+小马哥++ | 园豆:906 (小虾三级) | 2013-06-29 15:39

这个应该才是正解吧

支持(1) 反对(0) yuqi_xu | 园豆:208 (菜鸟二级) | 2013-08-16 17:57
0
楼上的看起来好复杂!!!我的解决方案如下:
SELECT NAME,C1=(SELECT COUNT(*) FROM t1 WHERE score1!=0 AND NAME=t1.name),SUM(score2),SUM(score3) FROM t1 GROUP BY NAME
幻天芒 | 园豆:36594 (高人七级) | 2013-06-29 18:22

精辟啊

支持(1) 反对(0) 百年老妖 | 园豆:184 (初学一级) | 2013-07-01 10:19

A 3 190 0
B 3 0 0
D 3 0 199

 

支持(0) 反对(0) t101lian | 园豆:18 (初学一级) | 2013-12-18 15:48
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
世界万物 | 园豆:276 (菜鸟二级) | 2013-06-30 23:39
0
select name,(select count(score1) from [tables] where score1!=0 )as c1,sum(score2),sum(score3),from [tables] t group by name
程序新青年 | 园豆:841 (小虾三级) | 2013-07-02 15:05
0

路过学习。

panjk | 园豆:712 (小虾三级) | 2013-07-17 14:18
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
t101lian | 园豆:18 (初学一级) | 2013-12-18 15:53
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
空紫竹 | 园豆:204 (菜鸟二级) | 2014-02-19 13:19
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册