問題：
一百個賬戶各有100$，某個賬戶某天如有支出則添加一條新記錄，記錄其余額。一百天后，請輸出每天所有賬戶的余額信息
這個問題的難點在于每個用戶在某天可能有多條紀錄，也可能一條紀錄也沒有（不包括第一天）
返回的記錄集是一個100天*100個用戶的紀錄集
下面是我的思路：
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 @i=@i+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
b.返回一個所有用戶名的紀錄集：
select distinct username from table2
返回結果：
username
--------------------------------------------------
1
10
100
......
99
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)
order by C.outdate,convert(int,C.username)
注意：用戶在當天如果沒有紀錄，cash字段返回NULL，否則cash返回每個用戶當天的余額
outdate username cash
2001-10-01 00:00:00.000 1 90
2001-10-01 00:00:00.000 2 100
......
2001-10-02 00:00:00.000 1 90
2001-10-02 00:00:00.000 2 NULL <--注意這里
......
2002-01-08 00:00:00.000 100 50
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)
order by C.outdate,convert(int,C.username)
返回結果：
outdate username cash
2001-10-01 00:00:00.000 1 90
2001-10-01 00:00:00.000 2 100
......
2002-01-08 00:00:00.000 100 50
大家看看還有沒什么bug，如果你發現bug或者你有更好的方法，你可能發郵件給我：hydnoahark.netease.com ^-^
延伸閱讀
文章來源于領測軟件測試網 http://www.kjueaiud.com/
