sql - 在 SQL Server 中将每三行分组为一组

Question

我有以下使用 SQL 查询的结果集，

查询

SELECT Rn = ROW_NUMBER() OVER
(
    ORDER BY Team_Id, Age DESC
), * 
FROM tblTeams;

结果

+----+---------+-------------+-----+
| Rn | Team_Id | Team_Member | Age |
+----+---------+-------------+-----+
| 1  |  1      |  Steven     |  35 |
| 2  |  1      |  Smith      |  33 |
| 3  |  1      |  David      |  32 |
| 4  |  1      |  Watson     |  30 |
| 5  |  2      |  Miller     |  31 |
| 6  |  2      |  Ryan       |  29 |
| 7  |  2      |  Benjamin   |  28 |
| 8  |  2      |  Wayne      |  27 |
| 9  |  3      |  James      |  36 |
| 10 |  3      |  Stuart     |  33 |
| 11 |  3      |  Alex       |  32 |
+----+---------+-------------+-----+

我想将每 3 行分组为一个组，如下所示。

预期输出

+----------+----+---------+-------------+-----+
| Group_Id | Rn | Team_Id | Team_Member | Age |
+----------+----+---------+-------------+-----+
| 1        | 1  |  1      |  Steven     |  35 |
| 1        | 2  |  1      |  Smith      |  33 |
| 1        | 3  |  1      |  David      |  32 |
| 2        | 4  |  1      |  Watson     |  30 |
| 2        | 5  |  2      |  Miller     |  31 |
| 2        | 6  |  2      |  Ryan       |  29 |
| 3        | 7  |  2      |  Benjamin   |  28 |
| 3        | 8  |  2      |  Wayne      |  27 |
| 3        | 9  |  3      |  James      |  36 |
| 4        | 10 |  3      |  Stuart     |  33 |
| 4        | 11 |  3      |  Alex       |  32 |
+----------+----+---------+-------------+-----+

Answer 1

尝试这个 -

架构

DECLARE @tblTeams TABLE
    (
      Team_Id   INT ,
      Team_Member VARCHAR(50),
      Age INT
    )

INSERT  INTO @tblTeams
VALUES  ( 1, 'Steven', 35 ),
        ( 1, 'Smith', 33 ),
        (1, 'David', 32), 
        ( 1, 'Watson', 30 ),
        ( 2, 'Miller', 33 ),
        (2, 'Ryan', 29),
        (2, 'Benjamin', 28),
        (2, 'Wayne', 25);

查询

SELECT ((t.Rn - 1) / 3) + 1 AS Group_Id, * FROM
(
    SELECT Rn = ROW_NUMBER() OVER
    (
        ORDER BY Team_Id, Age DESC
    ), * 
    FROM @tblTeams
) t

结果

+----------+----+---------+-------------+-----+
| Group_Id | Rn | Team_Id | Team_Member | Age |
+----------+----+---------+-------------+-----+
| 1        | 1  |  1      |  Steven     |  35 |
| 1        | 2  |  1      |  Smith      |  33 |
| 1        | 3  |  1      |  David      |  32 |
| 2        | 4  |  1      |  Watson     |  30 |
| 2        | 5  |  2      |  Miller     |  31 |
| 2        | 6  |  2      |  Ryan       |  29 |
| 3        | 7  |  2      |  Benjamin   |  28 |
| 3        | 8  |  2      |  Wayne      |  27 |