日期:2014-05-18 浏览次数:20554 次
declare @t table ( inv_code int,price numeric(6,4),amount numeric(5,2), amount_money numeric(10,6),date datetime ) insert into @t select 11100001,8.1111,9.40,76.240000,'2012-02-23 11:37:36.510' union all select 11100001,8.1111,0.11,0.890000,'2012-02-23 11:28:23.510' union all select 11100001,10.0000,3.00,30.000000,'2012-02-23 11:13:48.557' union all select 11100001,10.0000,2.00,20.000000,'2012-02-23 11:06:23.103' union all select 11100001,10.0000,13.00,130.000000,'2012-02-23 10:59:02.227' union all select 11100001,10.0000,10.00,100.000000,'2012-02-22 15:43:08.010' union all select 11100002,20.0000,100.00,2000.000000,'2012-02-07 15:26:09.577' union all select 11100002,10.0000,100.00,1000.000000,'2012-02-07 15:25:28.593' union all select 12100001,10.0000,20.00,200.000000,'2012-02-24 14:11:09.603' ;with maco as ( select row_number() over (partition by inv_code order by date desc) as rid,* from @t ),m as (select * from maco where rid<4) select inv_code, ltrim(max(case when rid=1 then price else null end))+','+ ltrim(max(case when rid=1 then amount else null end))+','+ ltrim(max(case when rid=1 then amount_money else null end)) as content1 , ltrim(max(case when rid=2 then price else null end))+','+ ltrim(max(case when rid=2 then amount else null end))+','+ ltrim(max(case when rid=2 then amount_money else 0 end)) as content2 , ltrim(max(case when rid=3 then price else null end))+','+ ltrim(max(case when rid=3 then amount else null end))+','+ ltrim(max(case when rid=3 then amount_money else null end)) as content3 from m group by inv_code /* inv_code content1 content2 content3 ----------- ------------------------------- ----------------------------- ------------------------- 11100001 8.1111,9.40,76.240000 8.1111,0.11,0.890000 10.0000,3.00,30.000000 11100002 20.0000,100.00,2000.000000 10.0000,100.00,1000.000000 NULL 12100001 10.0000,20.00,200.000000 NULL NULL */
------解决方案--------------------
--inv_code price amount amount_money date --11100001 8.1111 9.40 76.240000 2012-02-23 11:37:36.510 --11100001 8.1111 0.11 0.890000 2012-02-23 11:28:23.510 --11100001 10.0000 3.00 30.000000 2012-02-23 11:13:48.557 -- 11100001 10.0000 2.00 20.000000 2012-02-23 11:06:23.103 -- 11100001 10.0000 13.00 130.000000 2012-02-23 10:59:02