求教上这个SQL 如何实现排序

求教下这个SQL 怎么实现排序
我是在.net 版块http://topic.csdn.net/u/20120704/15/fde6f34d-8b3c-4094-a378-23d755518aaa.html?8077
看到的，发现自己也不会写，求大牛指点下这个SQL怎么写，我想过 row_number() over partition by 发现这个函数
只分了一次组排序，这个应该需要分2次组排序吧，一时感兴趣，自己也弄不出来求大牛指教.
id name type date
1 jim 1 2012-02-01
2 bob 1 2012-02-01
3 bob 1 2012-02-01
n jim 1 2012-02-02
n bob 1 2012-02-02
4 jim 2 2012-02-01
5 bob 2 2012-02-01
6 bob 2 2012-02-01
n jim 2 2012-02-02
n bob 2 2012-02-02
7 bob 3 2012-02-01
8 bob 3 2012-02-01
9 bob 3 2012-02-01
n jim 3 2012-02-02
n bob 3 2012-02-02

我想处理成。

1 jim 1 2012-02-01
4 jim 2 2012-02-01
7 bob 3 2012-02-01
2 bob 1 2012-02-01
5 bob 2 2012-02-01
8 bob 3 2012-02-01
3 bob 1 2012-02-01
6 bob 2 2012-02-01
9 bob 3 2012-02-01

补充下。。。是这样

[解决办法]

SQL code

--&gt; 测试数据：[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 allselect '2','bob',1,'2012-02-01' union allselect '3','bob',1,'2012-02-01' union allselect 'n','jim',1,'2012-02-02' union allselect 'n','bob',1,'2012-02-02' union allselect '4','jim',2,'2012-02-01' union allselect '5','bob',2,'2012-02-01' union allselect '6','bob',2,'2012-02-01' union allselect 'n','jim',2,'2012-02-02' union allselect 'n','bob',2,'2012-02-02' union allselect '7','bob',3,'2012-02-01' union allselect '8','bob',3,'2012-02-01' union allselect '9','bob',3,'2012-02-01' union allselect 'n','jim',3,'2012-02-02' union allselect 'n','bob',3,'2012-02-02'select * from (select *,px=ROW_NUMBER()over(partition by [type]order by id)from test where [id]&lt;&gt;'n')torder by px,[type]/*id    name    type    date    px1    jim    1    2012-02-01 00:00:00.000    14    jim    2    2012-02-01 00:00:00.000    17    bob    3    2012-02-01 00:00:00.000    12    bob    1    2012-02-01 00:00:00.000    25    bob    2    2012-02-01 00:00:00.000    28    bob    3    2012-02-01 00:00:00.000    23    bob    1    2012-02-01 00:00:00.000    36    bob    2    2012-02-01 00:00:00.000    39    bob    3    2012-02-01 00:00:00.000    3*/
[解决办法]
SQL code--&gt; 测试数据：[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 ALLSELECT '2','bob',1,'2012-02-01' UNION ALLSELECT '3','bob',1,'2012-02-01' UNION ALLSELECT 'n','jim',1,'2012-02-02' UNION ALLSELECT 'n','bob',1,'2012-02-02' UNION ALLSELECT '4','jim',2,'2012-02-01' UNION ALLSELECT '5','bob',2,'2012-02-01' UNION ALLSELECT '6','bob',2,'2012-02-01' UNION ALLSELECT 'n','jim',2,'2012-02-02' UNION ALLSELECT 'n','bob',2,'2012-02-02' UNION ALLSELECT '7','bob',3,'2012-02-01' UNION ALLSELECT '8','bob',3,'2012-02-01' UNION ALLSELECT '9','bob',3,'2012-02-01' UNION ALLSELECT 'n','jim',3,'2012-02-02' UNION ALLSELECT '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.0004    jim  2           2012-02-01 00:00:00.0002    bob  1           2012-02-01 00:00:00.0003    bob  1           2012-02-01 00:00:00.0005    bob  2           2012-02-01 00:00:00.0006    bob  2           2012-02-01 00:00:00.0007    bob  3           2012-02-01 00:00:00.0008    bob  3           2012-02-01 00:00:00.0009    bob  3           2012-02-01 00:00:00.000n    jim  1           2012-02-02 00:00:00.000n    jim  2           2012-02-02 00:00:00.000n    jim  3           2012-02-02 00:00:00.000n    bob  1           2012-02-02 00:00:00.000n    bob  2           2012-02-02 00:00:00.000n    bob  3           2012-02-02 00:00:00.000(15 行受影响)*/ 
 
[解决办法]
SQL code--&gt; 测试数据：[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 allselect '2','bob',1,'2012-02-01' union allselect '3','bob',1,'2012-02-01' union allselect 'n','jim',1,'2012-02-02' union allselect 'n','bob',1,'2012-02-02' union allselect '4','jim',2,'2012-02-01' union allselect '5','bob',2,'2012-02-01' union allselect '6','bob',2,'2012-02-01' union allselect 'n','jim',2,'2012-02-02' union allselect 'n','bob',2,'2012-02-02' union allselect '7','bob',3,'2012-02-01' union allselect '8','bob',3,'2012-02-01' union allselect '9','bob',3,'2012-02-01' union allselect 'n','jim',3,'2012-02-02' union allselect 'n','bob',3,'2012-02-02'select     id,    name,    [type],    convert(varchar(10),[date],120) [date]from    (        select             *,            px=ROW_NUMBER()over(partition by [type]order by id)        from             test         where             [id]&lt;&gt;'n'    )torder by     px,[type]/*id    name    type    date---------------------------------1    jim    1    2012-02-014    jim    2    2012-02-017    bob    3    2012-02-012    bob    1    2012-02-015    bob    2    2012-02-018    bob    3    2012-02-013    bob    1    2012-02-016    bob    2    2012-02-019    bob    3    2012-02-01*/
[解决办法]
SQL code--&gt; 测试数据：[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 ALLSELECT '2','bob',1,'2012-02-01' UNION ALLSELECT '3','bob',1,'2012-02-01' UNION ALLSELECT 'n','jim',1,'2012-02-02' UNION ALLSELECT 'n','bob',1,'2012-02-02' UNION ALLSELECT '4','jim',2,'2012-02-01' UNION ALLSELECT '5','bob',2,'2012-02-01' UNION ALLSELECT '6','bob',2,'2012-02-01' UNION ALLSELECT 'n','jim',2,'2012-02-02' UNION ALLSELECT 'n','bob',2,'2012-02-02' UNION ALLSELECT '7','bob',3,'2012-02-01' UNION ALLSELECT '8','bob',3,'2012-02-01' UNION ALLSELECT '9','bob',3,'2012-02-01' UNION ALLSELECT 'n','jim',3,'2012-02-02' UNION ALLSELECT 'n','bob',3,'2012-02-02'--------------开始查询--------------------------SELECT * FROM(SELECT *,row=ROW_NUMBER() over(PARTITION BY type ORDER BY [date]) FROM [tb] ) tORDER BY row,type----------------结果----------------------------/* id   name type        date                    row---- ---- ----------- ----------------------- --------------------1    jim  1           2012-02-01 00:00:00.000 14    jim  2           2012-02-01 00:00:00.000 17    bob  3           2012-02-01 00:00:00.000 12    bob  1           2012-02-01 00:00:00.000 25    bob  2           2012-02-01 00:00:00.000 28    bob  3           2012-02-01 00:00:00.000 23    bob  1           2012-02-01 00:00:00.000 36    bob  2           2012-02-01 00:00:00.000 39    bob  3           2012-02-01 00:00:00.000 3n    jim  1           2012-02-02 00:00:00.000 4n    jim  2           2012-02-02 00:00:00.000 4n    jim  3           2012-02-02 00:00:00.000 4n    bob  1           2012-02-02 00:00:00.000 5n    bob  2           2012-02-02 00:00:00.000 5n    bob  3           2012-02-02 00:00:00.000 5(15 行受影响)*/
[解决办法]
用的一楼的表结构
SQL codeselect id,name,type,convert(char(10),date,23) as date from (select *,RANK()over(partition by type order by id ) as row from test where id&lt;&gt;'n') aorder by row,type------------结果如下：----------1    jim    1    2012-02-014    jim    2    2012-02-017    bob    3    2012-02-012    bob    1    2012-02-015    bob    2    2012-02-018    bob    3    2012-02-013    bob    1    2012-02-016    bob    2    2012-02-019    bob    3    2012-02-01

求教上这个SQL 如何实现排序

热点推荐