日期:2014-05-18 浏览次数:20591 次
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]( [id] varchar(1), [name] varchar(3), [type] int, [date] datetime ) insert [test] select '1','jim',1,'2012-02-01' union all select '2','bob',1,'2012-02-01' union all select '3','bob',1,'2012-02-01' union all select 'n','jim',1,'2012-02-02' union all select 'n','bob',1,'2012-02-02' union all select '4','jim',2,'2012-02-01' union all select '5','bob',2,'2012-02-01' union all select '6','bob',2,'2012-02-01' union all select 'n','jim',2,'2012-02-02' union all select 'n','bob',2,'2012-02-02' union all select '7','bob',3,'2012-02-01' union all select '8','bob',3,'2012-02-01' union all select '9','bob',3,'2012-02-01' union all select 'n','jim',3,'2012-02-02' union all select 'n','bob',3,'2012-02-02' select * from ( select *,px=ROW_NUMBER()over(partition by [type]order by id) from test where [id]<>'n')t order by px,[type] /* id name type date px 1 jim 1 2012-02-01 00:00:00.000 1 4 jim 2 2012-02-01 00:00:00.000 1 7 bob 3 2012-02-01 00:00:00.000 1 2 bob 1 2012-02-01 00:00:00.000 2 5 bob 2 2012-02-01 00:00:00.000 2 8 bob 3 2012-02-01 00:00:00.000 2 3 bob 1 2012-02-01 00:00:00.000 3 6 bob 2 2012-02-01 00:00:00.000 3 9 bob 3 2012-02-01 00:00:00.000 3 */
------解决方案--------------------
--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] VARCHAR(1),[name] VARCHAR(3),[type] INT,[date] DATETIME) INSERT [tb] SELECT '1','jim',1,'2012-02-01' UNION ALL SELECT '2','bob',1,'2012-02-01' UNION ALL SELECT '3','bob',1,'2012-02-01' UNION ALL SELECT 'n','jim',1,'2012-02-02' UNION ALL SELECT 'n','bob',1,'2012-02-02' UNION ALL SELECT '4','jim',2,'2012-02-01' UNION ALL SELECT '5','bob',2,'2012-02-01' UNION ALL SELECT '6','bob',2,'2012-02-01' UNION ALL SELECT 'n','jim',2,'2012-02-02' UNION ALL SELECT 'n','bob',2,'2012-02-02' UNION ALL SELECT '7','bob',3,'2012-02-01' UNION ALL SELECT '8','bob',3,'2012-02-01' UNION ALL SELECT '9','bob',3,'2012-02-01' UNION ALL SELECT 'n','jim',3,'2012-02-02' UNION ALL SELECT 'n','bob',3,'2012-02-02' --------------开始查询-------------------------- SELECT * FROM [tb] ORDER BY [date],[name] DESC ,type ----------------结果---------------------------- /* id name type date ---- ---- ----------- ----------------------- 1 jim 1 2012-02-01 00:00:00.000 4 jim 2 2012-02-01 00:00:00.000 2 bob 1 2012-02-01 00:00:00.000 3 bob 1 2012-02-01 00:00:00.000 5 bob 2 2012-02-01 00:00:00.000 6 bob 2 2012-02-01 00:00:00.000 7 bob 3 2012-02-01 00:00:00.000 8 bob 3 2012-02-01 00:00:00.000 9 bob 3 2012-02-01 00:00:00.000 n jim 1 2012-02-02 00:00:00.000 n jim 2 2012-02-02 00:00:00.000 n jim 3