（转贴）微软的面试题 sql

蓝带鱼

最初由 kanmm 发布
[B]我告诉你最终答案，一定不会错，哈哈哈
create table trade_test (
t_date number(2),  -- 时间
t_id  varchar2(6),  -- 用户
t_c   number(5) );  -- 余额

select min(t_c) ,t_id
from trade_test
where t_date <3
group by t_id   

因为它只是有支出做记录，所以不会增加，大家看题！！！！！！！！ ：）
如果要是余额增加，那就不能这么设计了，嘿嘿
简单吧 [/B]

selct min(t_c),tid from trade_test where t_date<3
"where t_date<3"是什么意思？

kanmm

select sum(min(t_c))
from trade_test
where t_date <'2001-11-09'
group by t_id

做循环
刚才3是一个日期，呵呵

rollingpig

想用group肯定不行

假如大家都不消费

哪来那么多记录

还是得用循环!!

kanmm

如果考虑优化就不能这样建表了
微软就是故意难你

ttdb

最初由 蓝带鱼 发布
[B]

还要输出每一天的余额呢，应该是每一天时间最大的那一条，
这里就有一个问题，如果这一天没有任何支出呢？ [/B]

我觉得思路就是在这里，如何用sql语句来解决（而不是peocedure、循环、建表）

oldwain

本语句有错, 请看后面的帖子.
不删除, 留作存根!

最初由 kanmm 发布
[B]create table trade_test (
t_date number(2),  -- 时间
t_id  varchar2(6),  -- 用户
t_c   number(5) );  -- 余额

[/B]

很复杂的一道题.

引用上面的表结构, 只是为了方便, 将t_date改为date型.
[php]
create table trade_test (
     t_date date, -- 时间
     t_id varchar2(6), -- 用户
     t_c number(5) ) -- 余额
/
[/php]

下面是我的语句(满足ttdb说的不用循环, 不建立表),
[php]
select v_date, t_id, min(t_c2)  
from (
        select v_date, t_id, nvl(t_c,  
                (select t_c from trade_test where t_date =
                        ( select max(t_date)
                        from trade_test
                        where t_date < v_test1.v_date)
                )) t_c2
        from (
                select vd.v_date, tt.t_id, tt.t_c  
                from trade_test tt, (
                        select (sysdate - rownum) v_date
                        from all_objects
                        where rownum < 101)  vd
                where tt.t_date(+) = vd.v_date) v_test1
        )
group by v_date, t_id
/
[/php]


相当复杂, 下面把它分解成为以下部分:

[php]

-- vd视图用于建立一个包含一百个日期的表.
(select (sysdate - rownum) v_date from all_objects where rownum < 101) vd

-- 将vd与trade_test join到一起
(select vd.v_date, tt.t_id, tt.t_c  
from trade_test tt, v_date vd
where tt.t_date(+) = vd.v_date)  v_test1

-- 对于t_c为空的记录, 赋予之前最后一日的余额
select v_date, t_id, nvl(t_c,  
        select t_c from trade_test where t_date =
                ( select max(t_date)
                from trade_test
                where t_date < v_test1.v_date)
        )
from v_test1

[/php]

将三个视图合并起来即为上面的最终语句.


如果要是在面试的有限时间内, 我确信我是做不出来的.

kanmm

高人啊，有一个不太常用的指令，我需要学习
(+) join nvl 我很不熟，需要多多学习

oldwain

下午的语句经过考虑和测试, 证明有错误.
以下为经过测试的语句.

表结构:
[php]
create table trade_test (
        t_date date, -- 时间
        t_id varchar2(6), -- 用户
        t_c number(5) ) -- 余额
/
[/php]

测试样例:

INSERT INTO TRADE_TEST VALUES ( SYSDATE - 100, 'ABC', 100);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 100 + 1/24, 'ABC', 95);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 90, 'ABC', 90);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 90+ 1/24, 'ABC', 85);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 50, 'ABC', 50);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 30, 'ABC', 30);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 30 + 1/24, 'ABC', 25);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 10, 'ABC', 10);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 1, 'ABC', 5);
---
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 100, 'XYZ', 100);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 100 + 1/24, 'XYZ', 95);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 90, 'XYZ', 90);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 90+ 1/24, 'XYZ', 85);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 50, 'XYZ', 50);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 30, 'XYZ', 30);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 30 + 1/24, 'XYZ', 25);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 10, 'XYZ', 10);
INSERT INTO TRADE_TEST VALUES ( SYSDATE - 1, 'XYZ', 5);
COMMIT;


语句:
[php]
select v_id, to_char(v_date, 'YYYY-MM-DD'), min(v_c)
from
        (select v_id, v_date,
                NVL(VALL.t_c,
                        (        select t_c
                                from trade_test A
                                where A.t_id = VALL.v_id
                                and t_date =
                                        ( select max(t_date)
                                        from trade_test B
                                        where B.t_id = VALL.v_id and B.t_date <=

VALL.v_date
                                        )
                        )
                ) v_c
         from
                (
                        select vdid.v_date, vdid.v_id, tt.t_c
                        from         (
                                        select distinct vd.v_date, vid.v_id from
                                        (        select (sysdate - rownum) v_date
                                                from all_objects where rownum < 101
                                                union select distinct t_date v_date from

trade_test
                                        ) vd,
                                        (select distinct t_id v_id from trade_test) vid
                                ) vdid, trade_test tt
                        where vdid.v_date = tt.t_date(+)
                        and vdid.v_id = tt.t_id (+)
                ) VALL
        )VMIN
group by v_id, to_char(v_date, 'YYYY-MM-DD')
;
[/php]

说明:
vd: 形成一百天的日期与已有数据中日期的一个并集
vid: 所有的id号
vdid: 日期集合与id集合的一个笛卡儿集
VALL: 所有id所有日期的余额(无值的记录对应余额为null值)
VMIN: 将所有null值替换为最后一笔的余额
有了以上说明, 相信应该比较容易读懂, 不再分解.

语句在样例数据上测试正确(环境: oracle 8.17 + win2000 pro) . 由于篇幅所限, 不在列出测试结果, 朋友们可以自行测试.


语句未考虑优化, 有兴趣的朋友可以提出更好的解决方法. 如有错误, 请指正.

zchen88168

If it is from Microsoft, the first column in the table should be an ID column (Primary Key column). Also I created a table named test_balance with 4 columns. BalanceID is PK (Idenity column). GroupID is int. BalanceDate is datetime.  Amount is money.

Tehn following QUERY works. I tested on SQL Server. May need performance improved. Hav a try and get fun.

select tb1.balanceid, tb1.balancedate, tb1.groupid, tb1.amount
        from test_balance  tb1 where tb1.balanceid in
(select max(tb2.balanceid)  bid1
        from test_balance  tb2
        group by tb2.groupid)

lxhcnasia

我想微软的考题不可能是简单就可以做出来的，所以要好好的想一下。问题我会想几天再答：）