日期:2014-05-18 浏览次数:20586 次
create table S1(sheet_no int identity(1,1) ,pname nvarchar(10),oper_date datetime,amt decimal(18,1),paid decimal(18,1)) insert into S1 (pname,oper_date,amt,paid) values ('01','2012-02-01',60,5) insert into S1 (pname,oper_date,amt,paid) values ('01','2012-01-11',70,0) insert into S1 (pname,oper_date,amt,paid) values ('01','2012-02-21',80,0) insert into S1 (pname,oper_date,amt,paid) values ('01','2012-03-01',90,15) insert into S1 (pname,oper_date,amt,paid) values ('02','2012-02-01',81,0) insert into S1 (pname,oper_date,amt,paid) values ('02','2012-03-01',85,16) insert into S1 (pname,oper_date,amt,paid) values ('03','2012-01-11',80,0) insert into S1 (pname,oper_date,amt,paid) values ('03','2012-03-01',90,20)
create table S1(sheet_no int identity(1,1) ,pname nvarchar(10),oper_date datetime,amt decimal(18,1),paid decimal(18,1)) insert into S1 (pname,oper_date,amt,paid) values ('01','2012-02-01',60,5) insert into S1 (pname,oper_date,amt,paid) values ('01','2012-01-11',70,0) insert into S1 (pname,oper_date,amt,paid) values ('01','2012-02-21',80,0) insert into S1 (pname,oper_date,amt,paid) values ('01','2012-03-01',90,15) insert into S1 (pname,oper_date,amt,paid) values ('02','2012-02-01',81,0) insert into S1 (pname,oper_date,amt,paid) values ('02','2012-03-01',85,16) insert into S1 (pname,oper_date,amt,paid) values ('03','2012-01-11',80,0) insert into S1 (pname,oper_date,amt,paid) values ('03','2012-03-01',90,20) select pname 名称, sum(amt) 单据金额, sum(case datepart(mm,oper_date) when 1 then paid else 0 end) [1月已付金额], sum(case datepart(mm,oper_date) when 2 then paid else 0 end) [2月已付金额], sum(case datepart(mm,oper_date) when 3 then paid else 0 end) [3月已付金额] from s1 group by pname drop table s1 /* 名称 单据金额 1月已付金额 2月已付金额 3月已付金额 ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 01 300.0 .0 5.0 15.0 02 166.0 .0 .0 16.0 03 170.0 .0 .0 20.0 (所影响的行数为 3 行) */