關于論壇上那個SQL微軟面試題。我的解答方法 :-)

問題：

一百個賬戶各有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.anti-gravitydesign.com