关于论坛上那个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 ^-^

  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论

打赏作者

hydnoahark

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值