sql - 将行附加到 SQL 中的每个组

Question

假设我有数据库表:

| id         | value     | rank        |
| -----------|-----------|-------------|
| 303        | D         | 3           |
| 404        | A         | 1           |
| 505        | B         | 1           |
| 505        | D         | 4           |
| 202        | B         | 1           |
| 505        | A         | 5           |
| 303        | N         | 2           |
| 101        | A         | 1           |
| 505        | A         | 7           |
| 202        | A         | 6           |
| 202        | N         | 3           |
| 505        | N         | 3           |
| 202        | A         | 4           |
| 505        | A         | 2           |
| 202        | N         | 5           |
| 303        | A         | 1           |
| 505        | N         | 6           |
| 202        | A         | 2           |

以下:

SELECT *
FROM table_name
GROUP BY id
ORDER BY rank;

我得到:

| id         | value     | rank        |
| -----------|-----------|-------------|
| 101        | A         | 1           |
| 202        | B         | 1           |
| 202        | A         | 2           |
| 202        | N         | 3           |
| 202        | A         | 4           |
| 202        | N         | 5           |
| 202        | A         | 6           |
| 303        | A         | 1           |
| 303        | N         | 2           |
| 303        | D         | 3           |
| 404        | A         | 1           |
| 505        | B         | 1           |
| 505        | A         | 2           |
| 505        | N         | 3           |
| 505        | D         | 4           |
| 505        | A         | 5           |
| 505        | N         | 6           |
| 505        | A         | 7           |

但是，对于每个组，我想附加一行，其中包含取自 id 列的 value 列，以便生成的表格如下所示:

| id         | value     | rank        |
| -----------|-----------|-------------|
| 101        | A         | 1           |
| 101        | 101       | 2           |
| 202        | B         | 1           |
| 202        | A         | 2           |
| 202        | N         | 3           |
| 202        | A         | 4           |
| 202        | N         | 5           |
| 202        | A         | 6           |
| 202        | 202       | 7           |
| 303        | A         | 1           |
| 303        | N         | 2           |
| 303        | D         | 3           |
| 303        | 303       | 4           |
| 404        | A         | 1           |
| 404        | 404       | 2           |
| 505        | B         | 1           |
| 505        | A         | 2           |
| 505        | N         | 3           |
| 505        | D         | 4           |
| 505        | A         | 5           |
| 505        | N         | 6           |
| 505        | A         | 7           |
| 505        | 505       | 8           |

什么是 ANSI SQL(或大多数与数据库无关的)方法来完成此任务？

Answer 1

您不希望在初始集中使用 group by，因为您似乎希望返回所有行:

select "id", "value", "rank"
from T
union all
select "id", cast("id" as varchar(10)), max("rank") + 1
from T
group by "id"
order by "id", "rank";

为了好玩，您可以使用分组集来做到这一点:

select "id",
    grouping("rank"),
    case when grouping("rank") = 0 then min("value") else cast("id" as varchar(10)) end as "value",
    case when grouping("rank") = 0 then "rank" else max("rank") over (partition by "id") + 1 end as "rank"
from T
group by grouping sets ("id", "rank"), ("id")
order by "id", "rank";