SQLite多左连接嵌套查询GROUP_CONCAT结果不符，请求技术协助

我来帮你搞定这个查询难题，核心思路是先为每个出发日期生成统一的价格组合字符串，再按这个字符串分组，把价格组合相同的日期合并到一起。

先理清楚数据关联逻辑 #

三张表的关联链路是：depdates.date → 通过priceId关联prices → 再通过accomodationId关联accomodations。我们需要先把每个日期对应的所有住宿价格拼接成一个固定格式的字符串，再把拥有相同价格字符串的日期归为一组。

正确的查询SQL #

SELECT 
    GROUP_CONCAT(date, ', ') AS DepartureDates,
    price_group AS AccomodationPrices
FROM (
    -- 子查询：为每个日期生成对应的价格组合字符串
    SELECT 
        d.date,
        -- 按住宿ID排序后拼接，确保相同价格组合的字符串完全一致
        GROUP_CONCAT(a.detail || ' ' || p.amount, ', ') AS price_group
    FROM depdates d
    JOIN prices p ON d.priceId = p.id
    JOIN accomodations a ON p.accomodationId = a.id
    GROUP BY d.date
    ORDER BY a.id
) AS date_price_groups
GROUP BY price_group;

为什么要这么写？ #

1. 子查询的作用：遍历每个出发日期，关联对应的价格和住宿信息，用GROUP_CONCAT把该日期下的所有「住宿名称+价格」拼接成一个字符串。这里必须加上ORDER BY a.id——如果不指定排序，相同的价格组合可能因为拼接顺序不同被当成不同的分组，直接导致结果错误。
2. 外层查询的作用：把生成的「日期-价格组」结果，按价格组合字符串分组，再用GROUP_CONCAT把相同价格组合的日期合并成一个列，正好匹配你想要的输出格式。

执行后的预期结果 #

附：表结构与数据插入代码 #

CREATE TABLE accomodations (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, detail VARCHAR);
INSERT INTO accomodations (id, detail) VALUES (1, 'single room');
INSERT INTO accomodations (id, detail) VALUES (2, 'double room');
INSERT INTO accomodations (id, detail) VALUES (3, 'triple room');
INSERT INTO accomodations (id, detail) VALUES (4, 'family room');
INSERT INTO accomodations (id, detail) VALUES (5, 'child');

CREATE TABLE prices (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, amount DECIMAL (10, 2), accomodationId INTEGER);
INSERT INTO prices (id, amount, accomodationId) VALUES (1, 10, 1);
INSERT INTO prices (id, amount, accomodationId) VALUES (2, 20, 2);
INSERT INTO prices (id, amount, accomodationId) VALUES (3, 30, 3);
INSERT INTO prices (id, amount, accomodationId) VALUES (4, 40, 4);
INSERT INTO prices (id, amount, accomodationId) VALUES (5, 50, 5);
INSERT INTO prices (id, amount, accomodationId) VALUES (6, 110, 1);
INSERT INTO prices (id, amount, accomodationId) VALUES (7, 120, 2);
INSERT INTO prices (id, amount, accomodationId) VALUES (8, 130, 3);
INSERT INTO prices (id, amount, accomodationId) VALUES (9, 140, 4);
INSERT INTO prices (id, amount, accomodationId) VALUES (10, 150, 5);

CREATE TABLE depdates (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, date VARCHAR, priceId INTEGER);
INSERT INTO depdates (id, date, priceId) VALUES (1, '2021-01-01', 1);
INSERT INTO depdates (id, date, priceId) VALUES (2, '2021-01-01', 2);
INSERT INTO depdates (id, date, priceId) VALUES (3, '2021-01-01', 3);
INSERT INTO depdates (id, date, priceId) VALUES (4, '2021-01-01', 4);
INSERT INTO depdates (id, date, priceId) VALUES (5, '2021-01-01', 5);
INSERT INTO depdates (id, date, priceId) VALUES (6, '2021-02-02', 6);
INSERT INTO depdates (id, date, priceId) VALUES (7, '2021-02-02', 7);
INSERT INTO depdates (id, date, priceId) VALUES (8, '2021-02-02', 8);
INSERT INTO depdates (id, date, priceId) VALUES (9, '2021-02-02', 9);
INSERT INTO depdates (id, date, priceId) VALUES (10, '2021-02-02', 10);
INSERT INTO depdates (id, date, priceId) VALUES (11, '2021-03-03', 1);
INSERT INTO depdates (id, date, priceId) VALUES (12, '2021-03-03', 2);
INSERT INTO depdates (id, date, priceId) VALUES (13, '2021-03-03', 3);
INSERT INTO depdates (id, date, priceId) VALUES (14, '2021-03-03', 4);
INSERT INTO depdates (id, date, priceId) VALUES (15, '2021-03-03', 5);

内容的提问来源于stack exchange，提问作者Max