现在有一张表
订单编号 订单数量  订单时间  物品编号  交货时间
 A00001   50      2011-11-02  W001    2011-12-02
 A00001   60      2011-12-02  W001    2011-12-07
 A00001   70      2012-01-02  W001    2012-02-04
 A00002   80      2011-12-02  W002    2011-12-02
 A00003   90      2011-12-02  W002   2011-12-02
要求是根据当前时间获取最近的一次下的订单数据。
订单编号        订单数量    订单时间    物品编号     交货时间
 A00001            70      2012-01-02     W001       2012-02-04
 A00002/ A00003   170      2011-12-02     W002       2011-12-02/2011-12-02

declare @表 table 
(
    订单编号 varchar(6),
    订单数量 int,
    订单时间 datetime,
    物品编号 varchar(4),
    交货时间 datetime
)
insert into @表
select 'a00001',50,'2011-11-02','w001','2011-12-02' union all
select 'a00001',60,'2011-12-02','w001','2011-12-07' union all
select 'a00001',70,'2012-01-02','w001','2012-02-04' union all
select 'a00002',80,'2011-12-02','w002','2011-12-02' union all
select 'a00003',90,'2011-12-02','w002','2011-12-02'

select  订单编号 ,
        订单数量 ,
        订单时间 = convert(varchar(10), 订单时间, 120) ,
        物品编号 ,
        交货时间 = convert(varchar(10), 交货时间, 120)
from    @表 t
where   订单时间 = ( select max(订单时间)
                 from   @表
                 where  物品编号 = t.物品编号
               )
/*
订单编号   订单数量        订单时间       物品编号 交货时间
------ ----------- ---------- ---- ----------
a00002 80          2011-12-02 w002 2011-12-02
a00003 90          2011-12-02 w002 2011-12-02
a00001 70          2012-01-02 w001 2012-02-04
*/

------解决方案--------------------
SQL code
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([订单编号] varchar(6),[订单数量] int,[订单时间] varchar(10),[物品编号] varchar(4),[交货时间] varchar(10))
insert [tb]
select 'A00001',50,'2011-11-02','W001','2011-12-02' union all
select 'A00001',60,'2011-12-02','W001','2011-12-07' union all
select 'A00001',70,'2012-01-02','W001','2012-02-04' union all
select 'A00002',80,'2011-12-02','W002','2011-12-02' union all
select 'A00003',90,'2011-12-02','W002','2011-12-02'

;with t1 as(
select * from tb t 
where 订单时间=(select top 1 订单时间 from tb where 订单编号=t.订单编号 and 订单时间<getdate() order by 订单时间 desc)
)
select 
订单编号=stuff((select '/'+订单编号 from t1 where 订单时间=t.订单时间 and 物品编号=t.物品编号 for xml path('')),1,1,''),
订单数量=sum(订单数量),
订单时间,
物品编号,
交货时间=stuff((select '/'+交货时间 from t1 where 订单时间=t.订单时间 and 物品编号=t.物品编号 for xml path('')),1,1,'')
from t1 t
group by 订单时间,物品编号
/**
订单编号        订单数量        订单时间       物品编号 交货时间
A00001            70                2012-01-02        W001    2012-02-04
A00002/A00003    170                2011-12-02        W002    2011-12-02/2011-12-02
**/

------解决方案--------------------
SQL code
create table  #tb
(
    订单编号 varchar(6),
    订单数量 int,
    订单时间 date,
    物品编号 varchar(4),
    交货时间 date
)

insert into #tb
select 'a00001',50,'2011-11-02','w001','2011-12-02' union all
select 'a00001',60,'2011-12-02','w001','2011-12-07' union all
select 'a00001',70,'2012-01-02','w001','2012-02-04' union all
select 'a00002',80,'2011-12-02','w002','2011-12-02' union all
select 'a00003',90,'2011-12-02','w002','2011-12-02'
go
select * from #tb
go
with cte as
(select 订单编号,订单数量,订单时间,物品编号,交货时间 from
(select rowid=ROW_NUMBER()over(partition by 订单编号 order by 订单时间),* from #tb) a where rowid=1
) 
select 订单编号=STUFF((select '/'+订单编号 from cte where 物品编号=m.物品编号 for xml path('')),1,1,''),
订单数量=sum(订单数量),订单时间,物品编号,
交货时间=stuff((select '/'+convert(varchar(10),交货时