sql - 如何为两个字段值选择相同数量的行？

Question

给定下表：

| animal | id |
---------------
| Dog    | 1  |
| Cat    | 2  |
| Dog    | 3  |
| Dog    | 4  |
| Cat    | 5  |
| Dog    | 6  |
| Dog    | 7  |
| Dog    | 8  |
| Cat    | 9  |
| Dog    | 10 |

在不使用 UNION 的情况下，是否有任何方法可以选择行以返回相等数量的猫和狗，例如每个选择 3 个：

| animal | id |
---------------
| Dog    | 1  |
| Cat    | 2  |
| Dog    | 3  |
| Dog    | 4  |
| Cat    | 5  |
| Cat    | 9  |

如果无法从可用行中生产出偶数的每只动物，则应尽可能接近偶数。

标签： sqlpostgresql

Answer 1

您可以将row_number与 partition by 一起使用animal：

CREATE TABLE sample(animal, id) AS 
    VALUES 
        ('Dog', 1),
        ('Cat', 2),
        ('Dog', 3),
        ('Dog', 4),
        ('Cat', 5),
        ('Dog', 6),
        ('Dog', 7),
        ('Dog', 8),
        ('Cat', 9),
        ('Dog', 10);

WITH tmp AS (
    SELECT 
        *, 
        row_number() OVER (PARTITION BY animal ORDER BY id) AS ordinal 
    FROM 
        sample 
)
SELECT * FROM tmp WHERE ordinal <= 3;