SQL疑难问题---请高手帮忙
大家好：
sql菜鸟遇到这样一个问题，就是在表#t1中每一个fcardnumber对应多条记录，其中每条记录的funitnumber不同。
现在想出这样的结果：每个fcardnumber一条记录，按照ftime排序，将最近的两条记录的funitnumber列出。

--drop table #t1
create table #t1
(
fid int identity(1,1),
fcardnumber varchar(10),
funitnumber varchar(10),
ftime datetime not null
)
go

insert into #t1 select 'Card001','Unit003','2012-04-05 08:03:12.350'
insert into #t1 select 'Card001','Unit002','2012-03-15 08:13:15.730'
insert into #t1 select 'Card001','Unit001','2012-02-25 08:15:18.130'

insert into #t1 select 'Card002','Unit005','2012-04-05 08:03:12.350'
insert into #t1 select 'Card002','Unit003','2012-03-15 08:13:15.730'
insert into #t1 select 'Card002','Unit002','2012-02-25 08:15:18.130'
insert into #t1 select 'Card002','Unit004','2012-01-25 08:15:18.130'

希望最终的结果是这样的：
Card001 Unit003 Unit002
Card002 Unit005 Unit003

恳请帮忙，谢谢~~~

[解决办法]

SQL code

create table #t1(fid int identity(1,1),fcardnumber varchar(10),funitnumber varchar(10),ftime datetime not null)goinsert into #t1 select 'Card001','Unit003','2012-04-05 08:03:12.350'insert into #t1 select 'Card001','Unit002','2012-03-15 08:13:15.730'insert into #t1 select 'Card001','Unit001','2012-02-25 08:15:18.130'insert into #t1 select 'Card002','Unit005','2012-04-05 08:03:12.350'insert into #t1 select 'Card002','Unit003','2012-03-15 08:13:15.730'insert into #t1 select 'Card002','Unit002','2012-02-25 08:15:18.130'insert into #t1 select 'Card002','Unit004','2012-01-25 08:15:18.130';with tas(select fcardnumber,funitnumber from(select *,ROW_NUMBER()over(partition by fcardnumber order by ftime desc) as row_numfrom #t1)a where row_num<=2)select fcardnumber,REPLACE(funitnumber,'<',' ') as funitnumber from(SELECT *FROM (SELECT DISTINCT fcardnumber FROM t)AOUTER APPLY(    SELECT funitnumber= STUFF(REPLACE(REPLACE(            (   SELECT funitnumber FROM t N                WHERE fcardnumber = A.fcardnumber                FOR XML AUTO             ), '_x0023_t1 funitnumber="', ''), '"/>', ''), 1, 1, ''))N)a/*fcardnumber    funitnumberCard001    Unit003 Unit002Card002    Unit005 Unit003*/
[解决办法]
你的 库版本估计不是 SQL 2008 或2005.如果是的话，执行是没有问题的。