数据库表
id type
1 1
2 1
3 1
4 1
5 1
6 2
7 2
8 2
9 2
10 3
11 3
12 3
13 3
sql读取后显示成
1 1
6 2
10 3
2 1
7 2
11 3
3 1
8 2
12 3
4 1
9 2
13 3
如何实现?
id type
1 1
2 1
3 1
4 1
5 1
6 2
7 2
8 2
9 2
10 3
11 3
12 3
13 3
sql读取后显示成
1 1
6 2
10 3
2 1
7 2
11 3
3 1
8 2
12 3
4 1
9 2
13 3
如何实现?
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-28 13:57:46
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[type] int)
Insert tb
Select 1,1 union all
Select 2,1 union all
Select 3,1 union all
Select 4,1 union all
Select 5,1 union all
Select 6,2 union all
Select 7,2 union all
Select 8,2 union all
Select 9,2 union all
Select 10,3 union all
Select 11,3 union all
Select 12,3 union all
Select 13,3
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select rn=row_number()over(partition by type order by id),*
from tb
)
select id,type
from t
order by rn,type
/*
id type
----------- -----------
1 1
6 2
10 3
2 1
7 2
11 3
3 1
8 2
12 3
4 1
9 2
13 3
5 1(13 行受影响)
*/楼主少显示了一个数吧
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[type] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,1 union all
select 5,1 union all
select 6,2 union all
select 7,2 union all
select 8,2 union all
select 9,2 union all
select 10,3 union all
select 11,3 union all
select 12,3 union all
select 13,3
---查询---
select
id,
type
from
(select *,px=(select count(1)+1 from tb where type=t.type and id<t.id) from tb t) b
order by
px,
type ---结果---
id type
----------- -----------
1 1
6 2
10 3
2 1
7 2
11 3
3 1
8 2
12 3
4 1
9 2
13 3
5 1(所影响的行数为 13 行)
INSERT @TB
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 5, 1 UNION ALL
SELECT 6, 2 UNION ALL
SELECT 7, 2 UNION ALL
SELECT 8, 2 UNION ALL
SELECT 9, 2 UNION ALL
SELECT 10, 3 UNION ALL
SELECT 11, 3 UNION ALL
SELECT 12, 3 UNION ALL
SELECT 13, 3SELECT *,SEQ=(SELECT COUNT(*) FROM @TB WHERE type=T.type AND id<=T.id) INTO #
FROM @TB AS TSELECT id,type
FROM #
WHERE SEQ<=(SELECT MIN(SEQ) FROM (SELECT MAX(SEQ) AS SEQ FROM # GROUP BY type) T)
ORDER BY SEQ,type,idDROP TABLE #
/*
id type
----------- -----------
1 1
6 2
10 3
2 1
7 2
11 3
3 1
8 2
12 3
4 1
9 2
13 3(12 row(s) affected)
*/