SQL疑难有关问题-请高手帮忙

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
)
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'



;with t
as(
select fcardnumber,funitnumber from(
select *,ROW_NUMBER()over(partition by fcardnumber order by ftime desc) as row_num
from #t1)a where row_num<=2
)
select fcardnumber,REPLACE(funitnumber,'<',' ') as funitnumber from(
SELECT *FROM (SELECT DISTINCT fcardnumber FROM t)A
OUTER 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    funitnumber
Card001    Unit003 Unit002
Card002    Unit005 Unit003
*/

------解决方案--------------------
你的 库版本估计不是 SQL 2008 或2005.如果是的话，执行是没有问题的。

SQL疑难有关问题-请高手帮忙

相关推荐