经典Sql面试题收集

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

***********************************************************************************

取出sql表中第31到40的记录（以自动增长ID为主键） *从数据表中取出第n条到第m条的记录*/

declare @m int declare @n int

declare @sql varchar(800) set @m=40 set @n=31

set @sql='select top '+str(@m-@n+1) + '* from idetail where autoid not in(

select top '+ str(@n-1) + 'autoid from idetail)' exec(@sql)

select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id

--------------------------------------------------------------------------------

select top 10 * from t where id in (select top 40 id from t order by id) order by id desc

*******************************************************************************

一道面试题,写sql语句

有表a存储二叉树的节点,要用一条sql语句查出所有节点及节点所在的层. 表a

c1 c2 A ----------1 ---- ---- / \\ A B B C --------2 A C / / \\

B D D N E ------3 C E / \\ \\

D F F K I ---4 E I

D K C N

所要得到的结果如下

jd cs

----- ---- A 1 B 2 C 2 D 3 N 3 E 3 F 4 K 4 I 4

有高手指导一下,我只能用pl/sql写出来,请教用一条sql语句的写法 SQL> select c2, level + 1 lv 2 from test start 3 with c1 = 'A'

4 connect by c1 = prior c2 5 union

6 select 'A', 1 from dual 7 order by lv;

C2 LV

-- ---------- A 1 B 2 C 2 D 3 E 3 N 3 F 4 I 4 K 4

已选择9行。