sql - 按值对排序

Question

我有一组排名，按组和排名排序:

Group | Rank
------------
  A   |  1
  A   |  2
  A   |  3
  A   |  4
  A   |  5
  A   |  6
  B   |  1
  B   |  2
  B   |  3
  B   |  4
  C   |  1
  C   |  2
  C   |  3
  C   |  4
  C   |  5
  D   |  1
  D   |  2
  D   |  3
  D   |  4

我想交错分组，按组和等级排序，一次每组 n 个排名(这里，n=2):

Group | Rank
------------
  A   |  1
  A   |  2
  B   |  1
  B   |  2
  C   |  1
  C   |  2
  D   |  1
  D   |  2
  A   |  3
  A   |  4
  B   |  3
  B   |  4
  C   |  3
  C   |  4
  D   |  3
  D   |  4
  A   |  5
  A   |  6
  C   |  5

我已经通过循环和表变量获得了预期的结果(代码粘贴在这里是因为我在 SQL Fiddle 中遇到了一个非描述性语法错误):

CREATE TABLE Rankings([Group] NCHAR(1), [Rank] INT)

INSERT Rankings
VALUES 
('A',1),
('A',2),
('A',3),
('A',4),
('A',5),
('A',6),
('B',1),
('B',2),
('B',3),
('B',4),
('C',1),
('C',2),
('C',3),
('C',4),
('C',5),
('D',1),
('D',2),
('D',3),
('D',4)

-- input
DECLARE @n INT = 2 --number of group rankings per rotation
-- output
DECLARE @OrderedRankings TABLE([Group] NCHAR(1), Rank INT)

-- 

-- in-memory rankings.. we will be deleting used rows
DECLARE @RankingsTemp TABLE(GroupIndex INT, [Group] NCHAR(1), Rank INT)
INSERT @RankingsTemp 
SELECT 
  ROW_NUMBER() OVER (PARTITION BY Rank ORDER BY [Group]) - 1 AS GroupIndex,
  [Group],
  Rank
FROM Rankings
ORDER BY [Group], Rank

-- loop variables
DECLARE @MaxGroupIndex INT = (SELECT MAX(GroupIndex) FROM @RankingsTemp)
DECLARE @RankingCount INT = (SELECT COUNT(*) FROM @RankingsTemp)
DECLARE @i INT

WHILE(@RankingCount > 0)
BEGIN
  SET @i = 0;
  WHILE(@i <= @MaxGroupIndex)
  BEGIN
    INSERT INTO @OrderedRankings
    ([Group], Rank)
    SELECT TOP(@n)
      [Group],
      Rank
    FROM @RankingsTemp
    WHERE GroupIndex = @i;

    WITH T AS (
      SELECT TOP(@n) *
      FROM @RankingsTemp
      WHERE GroupIndex = @i
    );
    DELETE FROM T

    SET @i = @i + 1;
  END
  SET @RankingCount = (SELECT COUNT(*) FROM @RankingsTemp)
END

SELECT @RankingCount as RankingCount, @MaxGroupIndex as MaxGroupIndex

-- view results
SELECT * FROM @OrderedRankings

如何使用基于集合的方法(无循环，无表变量)实现所需的排序？

我使用的是 SQL Server Enterprise 2008 R2。

编辑:为了澄清，我需要每组不超过 n 行来连续显示。此查询的目标是产生一个排序，当按顺序读取时，提供每个组关于排名的相等表示(一次 n 行)。

Answer 1

也许是这样的……SQL FIDDLE

Order by
 Ceiling(rank*1.0/2), group, rank

上面的工作 fiddle (列名略有变化)

更新:被 int math... 烧毁了。现在应该工作了。通过乘以 1.0 强制将 int 转换为十进制，这样隐式转换就不会删除我需要的余数，以便天花板正确舍入。