日期:2014-05-17 浏览次数:20461 次
select (case when row=1 then rtrim(订单号) else '' end)订单号,商品ID,价格
from
(select row=row_number()over(partition by 订单号 order by getdate()),订单号,商品ID,价格 from tb)t
DECLARE @a TABLE(a VARCHAR(20),b VARCHAR(20),c INT)
INSERT @a SELECT '1001', 'A01', 10
UNION ALL SELECT '1001' ,'B01', 50
UNION ALL SELECT '1001' ,'B02', 37
UNION ALL SELECT '1002' ,'A02', 24
UNION ALL SELECT '1002','C01', 27
SELECT a=CASE WHEN exists(SELECT 1 FROM @a WHERE a=a.a AND b<a.b) THEN '' ELSE a end ,a as a1,b,c FROM @a a
ORDER BY 2,b
--result
/*a a1 b c
-------------------- -------------------- -------------------- -----------
1001 1001 A01 10
1001 B01 50
1001 B02 37
1002 1002 A02 24
1002 C01 27
(所影响的行数为 5 行)*/