- SQL code
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)pname 名称 amt 单据金额 paid 已付金额
实现效果
名称 总额 余额 1月已付金额 2月已付金额 3月已付金额
01
02
03
------解决方案--------------------
--不明白你的余额是怎么算算出来的.
- SQL code
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 s1group by pnamedrop table s1/*名称 单据金额 1月已付金额 2月已付金额 3月已付金额 ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 01 300.0 .0 5.0 15.002 166.0 .0 .0 16.003 170.0 .0 .0 20.0（所影响的行数为 3 行）*/