如何编写SQL查询获取共享同一list_id的关联key？

问题说明 #

现有表结构如下：

需求很明确：给定一个或多个key值，要查询出所有和这些key属于同一list_id的key，并按逗号拼接返回。比如给'a'就返回'a,b,c'，给'a,d'就返回'a,b,c,d,e'。

实现SQL #

MySQL版本 #

假设表名为target_table，直接用子查询定位目标list_id，再聚合结果：

SELECT GROUP_CONCAT(DISTINCT `key` ORDER BY `key` SEPARATOR ',') AS result_keys
FROM target_table
WHERE list_id IN (
    SELECT list_id
    FROM target_table
    WHERE `key` IN ('a', 'd') -- 替换成你要传入的key集合
);

其他数据库适配 #

• PostgreSQL：把GROUP_CONCAT换成STRING_AGG：

SELECT STRING_AGG(DISTINCT `key`, ',' ORDER BY `key`) AS result_keys
FROM target_table
WHERE list_id IN (
    SELECT list_id
    FROM target_table
    WHERE `key` IN ('a', 'd')
);

• SQL Server：可以用STRING_AGG（2017及以上版本）：

SELECT STRING_AGG(DISTINCT [key], ',') WITHIN GROUP (ORDER BY [key]) AS result_keys
FROM target_table
WHERE list_id IN (
    SELECT list_id
    FROM target_table
    WHERE [key] IN ('a', 'd')
);

逻辑说明 #

1. 子查询先找出所有给定key对应的list_id；
2. 外层查询筛选出这些list_id下的所有key；
3. 用字符串聚合函数把结果拼接成逗号分隔的字符串，同时去重并排序保证结果整洁。

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