有表如下:
Name Score
AA 50
AA -20
AA 40
AA -25想通过一个SQL语句实现这样的查询:
Name ZScore FScore
AA 90 -45谢谢
Name Score
AA 50
AA -20
AA 40
AA -25想通过一个SQL语句实现这样的查询:
Name ZScore FScore
AA 90 -45谢谢
解决方案 »
- 对象名 'loginTable' 无效。
- 存储过程执行出错
- case when then 小问题 谢谢了!:)
- 这段存储过程哪里出错了?
- 请问如何在插入和更新数据时要停止索引,待操作完成后再启用原来的索引
- 竟然没人知道?:::两表关联创建视图的问题,简单的,请指教??50分
- 问个很菜的问题
- WMI事件报警 的问题
- sql server 的execute,sp_executesql方法如何从动态select语句中取得返回值???
- 请问如何知道,我SQL SERVER 2000 的语言ID 时多少
- 求一个一次update多条数据的存储过程的程序,数据库是SQL SERVER.
- 不把计算机连到网上,本机程序可以连本机的SQLServer服务器吗?
Name,
ZScore=sum(case when Score>0 then Score else 0 end),
FScore=sum(case when Score<0 then Score else 0 end)
from
表
group by
Name
insert into @t select 'AA',50
insert into @t select 'AA',-20
insert into @t select 'AA',40
insert into @t select 'AA',-25select
Name,
ZScore=sum(case when Score>0 then Score else 0 end),
FScore=sum(case when Score<0 then Score else 0 end)
from
@t
group by
Name/*
Name ZScore FScore
---- ----------- -----------
AA 90 -45
*/
create table T(name varchar(100), score int)
insert into T
select 'AA', 50 union all
select 'AA', -20 union all
select 'AA', 40 union all
select 'AA', -25select
Name,
sum(case when Score>0 then Score else 0 end) as ZScore,
sum(case when Score<0 then Score else 0 end) as FScore
from T
group by Namedrop table T
name varchar(10),
Score int
)insert tb select 'AA', 50union all select 'AA', -20
union all select 'AA', 40
union all select 'AA', -25select Name,
ZScore=sum( case when Score>0 then Score end),
FScore=sum(case when Score<0 then Score end)
from tb
group by Name
select name ,sum(case when score>0 then score else 0 end )as ZScore,sum(case when score<0 then score else 0 end )as FScore from tablename group by name