我写出来了,就涉及到两张表(pay与project)(project用来显示项目名称)
用的就是case when
可是感觉语句蛮长的,有没有更简单的?
SELECT pro.ProName,SUM(PayMoney)AS PayMoney,
SUM(CASE WHEN PayState<>'0'THEN PayMoney END)AS KMoney,
SUM(CASE WHEN PayState='0' THEN PayMoney END)AS NKMoney,
SUM(CASE WHEN OffType='房屋补偿' THEN PayMoney END)AS FMONEY,
SUM(CASE WHEN OffType='奖金' THEN PayMoney END)AS JMONEY,
SUM(CASE WHEN OffType='增补' THEN PayMoney END)AS ZMONEY
FROM dbo.Pay p RIGHT JOIN Project pro ON p.ProID=pro.ID
WHERE PayDDate BETWEEN '2011-04-05' AND '2012-04-19'
GROUP BY pro.ProName
select proid,sum(case paystate when 0 then 0 else paymoney end),sum(case paystate when 0 then paymoney else 0 end),sum(case paytype when '房屋补偿' then paymoney else 0 end) from Pay where 日期范围条件