IDC资讯中心
问题：
一百个账户各有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 ^-^
