關於論壇上那個SQL微軟面試題。我的解答方法 :-) (轉)[@more@]

問題：

一百個賬戶各有100$，某個賬戶某天如有支出則新增一條新記錄，記錄其餘額。一百天後，請輸出每天所有賬戶的餘額資訊

這個問題的難點在於每個在某天可能有多條紀錄，也可能一條紀錄也沒有（不包括第一天）

返回的記錄集是一個100天*100個使用者的紀錄集

下面是我的思路：

1.建立表並插入測試資料：我們要求username從1-100
CREATE TABLE [o].[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)

* from table2 order by outdate,convert(int,username)

2.組合查詢語句：
a.我們必須返回一個從第一天開始到100天的紀錄集：
如：2001-10-1（這個日期是任意的）到 2002-1-8
由於第一天是任意一天，所以我們需要下面的語句：
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或者你有更好的方法，你可能發給我：to:hydnoaharkease.com">hydnoahark@netease.com ^-^

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

相關文章