澳门二十一点游戏赌场 澳门二十一点游戏赌场
    您现在的位置:首页 >> 软件应用 >> SQL教程 >> 内容

    关于论坛上那个SQL微软面试题。我的解答方法 :-)

    时间:2012/7/23 20:07:42 点击:

      核心提示:问题:一百个账户各有100$,某个账户某天如有支出则添加一条新记录,记录其余额。一百天后,请输出每天所有账户的余额信息这个问题的难点在于每个用户在某天可能有多条纪录,也可能一条纪录也没有(不包括第一天...

    问题:

    一百个账户各有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相关评论
    发表我的评论
    • 大名:
    • 内容:
  • 澳门二十一点游戏赌场(www.0769online.com) © 2018 版权所有 All Rights Reserved. 邮箱:103150@qq.com 业务QQ:103150 手机:13549753683

    Power By Laoy8 SQL 3.0SP1 粤ICP备10098668号
  •