表结构设计的相当有问题.
datetime charge type
getdate() 60 1
getdate() 100 2
getdate() 50 1这样谁知道哪笔预付款对应哪个余额难道是依赖于插入顺序? 1-2,3-4,5-6 这样两两为一对?
如果两两为一对,那么你所说的,第一笔余款... 我自己都不知道怎么说了.
datetime charge type
getdate() 60 1
getdate() 100 2
getdate() 50 1这样谁知道哪笔预付款对应哪个余额难道是依赖于插入顺序? 1-2,3-4,5-6 这样两两为一对?
如果两两为一对,那么你所说的,第一笔余款... 我自己都不知道怎么说了.
--不知道是不是這樣理解,寫的太亂了:(create table T(date datetime,value int, type int)
insert into T select '2007-10-1',10,1
insert into T select '2007-10-1',20,1
insert into T select '2007-10-1',30,1
insert into T select '2007-10-1',100,2
insert into T select '2007-10-1',50,1
insert into T select '2007-10-1',100,2
insert into T select '2007-10-1',20,1
insert into T select '2007-10-1',30,2
insert into T select '2007-10-2',10,1
insert into T select '2007-10-2',20,1
insert into T select '2007-10-2',30,2
insert into T select '2007-10-2',40,1
insert into T select '2007-10-2',50,1
insert into T select '2007-10-2',60,2
GOselect id=identity(int,1,1) ,*
into #
from Tselect case when temp=3 then '小計' when temp=4 then '總計' else convert(char(10),date,120) end as riqi,
total, yuding,yukuan
from
(
select a.date, sum(value) as total,
sum(case when type=1 then value else 0 end) as yuding,
sum(case when type=2 then value else 0 end) as yukuan,
temp=1
from # a
inner join (select date,min(id) as id from # where type=2 group by date) b
on a.id<=b.id and a.date=b.date
group by a.dateunion allselect c.date,
value,
case when type=1 then value end,
case when type=2 then value end,
temp=2
from # c
inner join (select date,min(id) as id from # where type=2 group by date) d
on c.id>d.id and c.date=d.dateunion allselect e.date,
sum(value) as value,
sum(case when type=1 then value else 0 end),
sum(case when type=2 then value else 0 end),
temp=3
from # e
group by e.dateunion all select '9999-1-1' ,
sum(value) as value,
sum(case when type=1 then value else 0 end),
sum(case when type=2 then value else 0 end),
temp=4
from # f
) X
order by [date],temp
/*
2007-10-01 160 60 100
2007-10-01 50 50 NULL
2007-10-01 100 NULL 100
2007-10-01 20 20 NULL
2007-10-01 30 NULL 30
小計 360 130 230
2007-10-02 60 30 30
2007-10-02 40 40 NULL
2007-10-02 50 50 NULL
2007-10-02 60 NULL 60
小計 210 120 90
總計 570 250 320*/drop table T,#
好象差不多。我试下。现在我下班了。回去看。如果可以,分不会少~谢谢先~呵呵