1.創建表并插入測試數據：我們要求username從1-100 create table [dbo].[table2] ( [username] [varchar] (50) not null , --用戶名 [outdate] [datetime] not null , --日期 [cash] [float] not null --余額 ) on [primary
declare @i int set @i=1 while @i<=100 begin insert table2 values(convert(varchar(50),@i),'2001-10-1',100) insert table2 values(convert(varchar(50),@i),'2001-11-1',50) set @[email protected]+1 end insert table2 values(convert(varchar(50),@i),'2001-10-1',90)
select * from table2 order by outdate,convert(int,username)
2.組合查詢語句： a.我們必須返回一個從第一天開始到100天的紀錄集： 如：2001-10-1（這個日期是任意的） 到 2002-1-8 由于第一天是任意一天，所以我們需要下面的sql語句： select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate from table2 group by username order by convert(int,username) 這里的奧妙在于： convert(int,username)-1（記得我們指定用戶名從1-100 :-)) group by username,min(outdate):第一天就可能每個用戶有多個紀錄。 返回的結果： outdate ------------------------------------------------------ 2001-10-01 00:00:00.000 ......... 2002-01-08 00:00:00.000
c.返回一個100天記錄集和100個用戶記錄集的笛卡爾集合： select * from ( select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate from table2 group by username order by convert(int,username) ) as a cross join ( select distinct username from table2 ) as b order by outdate,convert(int,username) 返回結果100*100條紀錄： outdate username 2001-10-01 00:00:00.000 1 ...... 2002-01-08 00:00:00.000 100
d.返回當前所有用戶在數據庫的有的紀錄： select outdate,username,min(cash) as cash from table2 group by outdate,username
order by outdate,convert(int,username) 返回紀錄： outdate username cash 2001-10-01 00:00:00.000 1 90 ...... 2002-01-08 00:00:00.000 100 50
e.將c中返回的笛卡爾集和d中返回的紀錄做left join: select c.outdate,c.username, d.cash from ( select * from ( select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate from table2 group by username order by convert(int,username) ) as a cross join ( select distinct username from table2 ) as b ) as c left join ( select outdate,username,min(cash) as cash from table2 group by outdate,username ) as d on(c.username=d.username and datediff(d,c.outdate,d.outdate)=0)
f.好了，現在我們最后要做的就是，如果cash為null，我們要返回小于當前紀錄日期的第一個用戶余額(由于我們使用order by cash,所以返回top 1紀錄即可，使用min應該也可以)，這個余額即為當前的余額： case isnull(d.cash,0) when 0 then ( select top 1 cash from table2 where table2.username=c.username and datediff(d,c.outdate,table2.outdate)<0 order by table2.cash ) else d.cash end as cash
g.最后組合的完整語句就是 select c.outdate,c.username, case isnull(d.cash,0) when 0 then ( select top 1 cash from table2 where table2.username=c.username and datediff(d,c.outdate,table2.outdate)<0 order by table2.cash ) else d.cash end as cash from ( select * from ( select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate from table2 group by username order by convert(int,username) ) as a cross join ( select distinct username from table2 ) as b ) as c left join ( select outdate,username,min(cash) as cash from table2 group by outdate,username ) as d on(c.username=d.username and datediff(d,c.outdate,d.outdate)=0)
