group by可以得到分组结果

[ruan_hg]

如何让各组的记录交错排列呢？
例如：
1 red
2 red
3 red
4 blue
5 blue
6 blue
7 green
8 green
9 green
现在需要得到：
1 red
4 blue
7 green
2 red
5 blue
8 green
3 red
6 blue
9 green

[liangCK]

--> liangCK小梁 于2008-11-04
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,Color VARCHAR(5))
INSERT INTO @T
SELECT 1,'red' UNION ALL
SELECT 2,'red' UNION ALL
SELECT 3,'red' UNION ALL
SELECT 4,'blue' UNION ALL
SELECT 5,'blue' UNION ALL
SELECT 6,'blue' UNION ALL
SELECT 7,'green' UNION ALL
SELECT 8,'green' UNION ALL
SELECT 9,'green'

--SQL查询如下:

SELECT *
FROM @T
ORDER BY ROW_NUMBER() OVER(PARTITION BY Color ORDER BY id),id

/*
id          Color
----------- -----
1           red
4           blue
7           green
2           red
5           blue
8           green
3           red
6           blue
9           green


*/

[liangCK]

--> liangCK小梁 于2008-11-04
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,Color VARCHAR(5))
INSERT INTO @T
SELECT 1,'red' UNION ALL
SELECT 2,'red' UNION ALL
SELECT 3,'red' UNION ALL
SELECT 4,'blue' UNION ALL
SELECT 5,'blue' UNION ALL
SELECT 6,'blue' UNION ALL
SELECT 7,'green' UNION ALL
SELECT 8,'green' UNION ALL
SELECT 9,'green'

--SQL查询如下:

SELECT *
FROM @T AS t
ORDER BY (SELECT COUNT(*)
          FROM @T
          WHERE Color=t.Color
              AND id<=t.id),id

/*
id          Color
----------- -----
1           red
4           blue
7           green
2           red
5           blue
8           green
3           red
6           blue
9           green

(9 行受影响)


*/