select clientname
,sum(totalmoney)-sum(paymoney)
,sum(case datediff(m,getdate(),salesdate) when 0 then paymoney else 0 end)
from A
,sum(totalmoney)-sum(paymoney)
,sum(case datediff(m,getdate(),salesdate) when 0 then paymoney else 0 end)
from A
,sum(totalmoney)-sum(paymoney)
,sum(case datediff(m,getdate(),salesdate) when 0 then paymoney else 0 end)
from A
group by clientname
写掉了个group by..
from tb
group by convert(varchar(7),salesdate,120)
,sum(totalmoney)-sum(paymoney)
,sum(case datediff(m,getdate(),salesdate) when 0 then paymoney else 0 end)
from A group by A
if object_id('tb') is not null drop table tb
create table tb (clientname varchar(50),totalmoney money,paymoney money,salesdate datetime)
insert into tb
select '01',50,10,'2009-01-01' union all
select '01',80,20,'2009-01-05' union all
select '02',100,30,'2009-02-01' union all
select '02',200,40,'2009-02-04' union all
select '01',200,50,'2009-02-01'select clientname, sum(totalmoney)-sum(paymoney) as 差价,sum(paymoney) as 月和
from tb group by convert(varchar(7),salesdate,120),clientname/*---
01 100.00 30.00 (一月)
01 150.00 50.00 (二月)
02 230.00 70.00 (二月)