select distinct a.id,sum(a.fz) , (select sum(b.fz) from table_a as b
where b.fz < 0 and a.id = b.id) from table_a as a where a.fz>0
where b.fz < 0 and a.id = b.id) from table_a as a where a.fz>0
解决方案 »
- SQL Server
- 问一个数据库设计问题。
- 请问为什么我的sql sp3总是安装不上?
- 简单是select查询咨询
- 打开SQL的企业管理器后,过一会就自动关闭
- ●●●●数据库截取字符串●●●● ---在线等!!!
- 嘿嘿 大虾门解决下呵呵
- 关于数据库中数据转移问题!在线等,分不够还可以加!急
- 关于mysql中使用ANSI SQL的几个问题
- 想进入Oracle 的世界吗?http://coug.126.com
- 如何用sql语句动态创建临时表、动态声明变量
- 在ms sql数据库中,一个企业名称表,有2万多条记录,要在另26个表中(每个表都有至少几十万条记录)查找指定月份有没有企业记录 把26个表中
(select sum(fz),id from table_a where fz>0) a,
(select sum(fz),id from table_a where fz<0) b where a.id=b.id
但是引发另外一个问题,你那种方法选择出来的是id既有正分又有副分的,如果id分数为全正或者全副则选择不出来。我改进一下可以解决这个问题:
select id, a.JF,b.KF from
(select sum(fz) as JF,id from table_a where fz>0 group by id) as a
left join
(select sum(fz) as KF,id from table_a where fz<0 group by id) as b
unionselect id, a.JF,b.KF from
(select sum(fz) as JF,id from table_a where fz>0 group by id) as a
right join
(select sum(fz) as KF,id from table_a where fz<0 group by id) as b
select id,a.sum(fz) as '加分',b.sum(fz) as '减分'from
(select sum(fz),id from table_a where fz>0) a
full outer join
(select sum(fz),id from table_a where fz<0) b on a.id=b.id
,JF=sum(case when FZ>0 then FZ else 0 end)
,KF=sum(case when FZ<0 then FZ else 0 end)
from table_a group by theid