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

) 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