SQLite中如何限制GROUP_CONCAT的子查询结果数量

我用子查询为GROUP_CONCAT提供数据，目前排序功能正常，但LIMIT并未达到预期效果。测试代码如下：

CREATE TABLE IF NOT EXISTS Animal (
    id  TEXT    PRIMARY KEY,
    diet    TEXT
    );
INSERT INTO Animal VALUES ('monkey',    'herbivore');
INSERT INTO Animal VALUES ('goat',  'herbivore');
INSERT INTO Animal VALUES ('cat',   'carnivore');
INSERT INTO Animal VALUES ('dog',   'omnivore');
INSERT INTO Animal VALUES ('human', 'omnivore');

CREATE TABLE IF NOT EXISTS Domicile (
    id  TEXT    PRIMARY KEY REFERENCES Animal,
    home    TEXT
    );

INSERT INTO Domicile VALUES ('monkey',  'jungle');
INSERT INTO Domicile VALUES ('goat',    'farmyard');
INSERT INTO Domicile VALUES ('cat', 'house');
INSERT INTO Domicile VALUES ('dog', 'garden');
INSERT INTO Domicile VALUES ('human',   'house');

CREATE TABLE IF NOT EXISTS Food (
    name    TEXT    PRIMARY KEY
    );

INSERT INTO Food VALUES ('fruit');
INSERT INTO Food VALUES ('meat');
INSERT INTO Food VALUES ('fish');
INSERT INTO Food VALUES ('bread');
INSERT INTO Food VALUES ('sausages');

CREATE TABLE IF NOT EXISTS Eats (
    id  TEXT    REFERENCES Animal,
    name    TEXT    REFERENCES Food
    );

INSERT INTO Eats VALUES ('monkey',  'fruit');
INSERT INTO Eats VALUES ('monkey',  'bread');
INSERT INTO Eats VALUES ('goat',    'fruit');
INSERT INTO Eats VALUES ('cat',     'meat');
INSERT INTO Eats VALUES ('cat',     'sausages');
INSERT INTO Eats VALUES ('dog',     'meat');
INSERT INTO Eats VALUES ('dog',     'bread');
INSERT INTO Eats VALUES ('dog',     'sausages');
INSERT INTO Eats VALUES ('human',   'fruit');
INSERT INTO Eats VALUES ('human',   'meat');
INSERT INTO Eats VALUES ('human',   'fish');
INSERT INTO Eats VALUES ('human',   'bread');
INSERT INTO Eats VALUES ('human',   'sausages');

.mode column

SELECT 'TEST 4: JOIN Eats using a subquery to order the names.';
SELECT      Animal.id,
        Animal.diet,
        GROUP_CONCAT (Eats.name) as names,
        Domicile.home
FROM        Animal
        INNER JOIN Domicile USING (id)
        INNER JOIN (
            SELECT * FROM Eats
            ORDER BY name
            ) as Eats USING (id)
GROUP BY    Animal.id
ORDER BY    Animal.id
;
SELECT 'TEST 4 result: names in order, but not limited to 3.';

SELECT 'TEST 5: LIMIT Eats subquery to 3.';
SELECT      Animal.id,
        Animal.diet,
        GROUP_CONCAT (Eats.name) as names,
        Domicile.home
FROM        Animal
        INNER JOIN Domicile USING (id)
        INNER JOIN (
            SELECT * FROM Eats
            ORDER BY name
            LIMIT 3
            ) as Eats USING (id)
GROUP BY    Animal.id
ORDER BY    Animal.id
;
SELECT 'TEST 5 result: LIMIT has applied to wrong SELECT?.';

当前问题 #

• TEST 4能实现names列的排序，但无法限制每个分组的元素数量
• TEST 5的LIMIT作用于整个Eats子查询，而非每个Animal分组，导致结果缺失大量数据

需求 #

1. 结果行按id排序
2. Eats表中并非所有Animal都有多个关联记录（例如山羊仅食用水果）
3. names列需按字母顺序排列，且最多包含3个元素（例如人类原本关联5种食物，需限制为前3个）

解决方案 #

SQLite中没有直接的分组内LIMIT语法，可通过**窗口函数ROW_NUMBER()**实现每个分组内的记录编号，再筛选出指定数量的记录后进行GROUP_CONCAT。

完整SQL代码如下：

.mode column

SELECT 
    Animal.id,
    Animal.diet,
    GROUP_CONCAT(Eats.name ORDER BY Eats.name) as names,
    Domicile.home
FROM Animal
INNER JOIN Domicile USING (id)
INNER JOIN (
    SELECT 
        id,
        name,
        -- 按Animal分组，食物名称排序后编号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY name) as rn
    FROM Eats
) as Eats USING (id)
-- 保留每个分组内前3条记录
WHERE Eats.rn <= 3
GROUP BY Animal.id
ORDER BY Animal.id;

代码说明 #

1. 子查询中，ROW_NUMBER() OVER (PARTITION BY id ORDER BY name)会为每个Animal的食物记录按名称排序后分配唯一编号，每个Animal的编号从1开始
2. 主查询通过WHERE Eats.rn <= 3筛选出每个Animal的前3种食物，若某个Animal的食物不足3种，则保留全部
3. GROUP_CONCAT(Eats.name ORDER BY Eats.name)显式指定拼接顺序，确保names列始终按字母排序
4. 若要修改限制数量，仅需调整WHERE Eats.rn <= 3中的数字即可，灵活性高

执行结果 #

运行后将得到期望输出：

id      diet       names                home
------  ---------  -------------------  --------
cat     carnivore  meat,sausages        house
dog     omnivore   bread,meat,sausages  garden
goat    herbivore  fruit                farmyard
human   omnivore   bread,fish,fruit     house
monkey  herbivore  bread,fruit          jungle

内容的提问来源于stack exchange，提问作者Norman of Anstruther