如有一张费用表(TPAY),有个客户字段(CUSTID),一个金额字段(MONEY),一个标志字段(FLAG;1:有效;2:失效...),如何在一条语句中根据客户号及标志汇总金额。效果:
客户 有效 失效
X Y Z
X1 Y1 Z1
...主要用于报表。
客户 有效 失效
X Y Z
X1 Y1 Z1
...主要用于报表。
解决方案 »
- 提示:ambiguous overloaded call to StrLIComp,如何修改?
- 多线程查询数据库,偶尔会丢掉一个线程。但大部分时候是正常。苦恼中。
- Delphi中的常量定义
- 请教高手一个奇怪问题,关于指针的
- 在WebBrowser打开的页面上,点击右键 看属性, 结果程序无法关闭了。这是怎么回事??怎么办??
- 高分求一SQL语句的写法?
- 高分求教在题库中随机抽题的算法问题
- FireMonkey程序的问题~
- 谁有FastRepor Clx的密码
- 托盘图标好像是从属于application的,单独的dll好像不能有dll
- 一个会用MapInfo或MapX和Delphi结合进行开发的人的月薪
- 我想创建一张VFP格式的数据表格
sum(case flag when 1 then 1 else 0 end) as 有效,
sum(case flag when 2 then 1 else 0 end) as 无效
from table group by custid
ORACLE:
select custid,
sum(decode(flag,1,0,money,0) as 有效,
sum(decode(flag,0,1,0,money) as 无效
from table group by custid
sqlserver:
select custid,
sum(case flag when 1 then 1 else 0 end) as 有效,
sum(case flag when 2 then 1 else 0 end) as 无效
from table group by custid
DB2:
select custid,
sum(case flag when ‘1' then money else 0) as 有效,
sum(case flag when ‘0' then money else 0) as 无效
from table group by custid
sum(case flag when 1 then Money else 0 end) as 有效金额,
sum(case flag when 2 then Money else 0 end) as 无效金额
from table group by custid
有效=SUM(CASE WHEN Flag=1 THEN MONEY ELSE 0 END),
无效=SUM(CASE WHEN Flag=2 THEN MONEY ELSE 0 END),
FROM TPAY
GROUP BY CustID
望了补充了,我用的数据库是sybase,但版本较低,不支持Case语句。
有无其它方法?
from (select custid acustid, sum(money) amoney
from table
where flag = 1),
(select custid bcustid, sum(money) bmoney
from table
where flag = 2)
where acustid = bcustid没有测试,不知是否可以
select custid,
(select sum(money) where custid=a.custid and flag=1) as 有效,
(select sum(money) where custid=a.custid and flag=2) as 无效
from table a group by custid
(select sum(money) from table where custid=a.custid and flag=1) as 有效,
(select sum(money) from table where custid=a.custid and flag=2) as 无效
from table a group by custid