mysql - 您可以 GROUP_CONCAT IF SELECT 语句的结果吗

Question

我想连接在查询中找到的结果。问题是我使用 IF 语句根据该条目的另一列的值对结果进行别名。

这个效果很好

SELECT town
IF(is_found = 0, name, null) as not_found,
IF(is_found = 1, name, null) as found
FROM listing

但是，如果我想按城镇对结果进行分组，我需要连接姓名列表的结果。

我尝试过这个:

SELECT town
GROUP_CONCAT(IF(is_found = 0, name, null) as not_found) as missing_list,
GROUP_CONCAT(IF(is_found = 1, name, null) as found) as found_list
FROM listing

我遇到语法错误。

如何生成这样的连续列表，其中结果以 IF 语句为条件。

Answer 1

as 不会出现在 if 中。所以你可以这样做:

SELECT town, 
       GROUP_CONCAT(IF(is_found = 0, name, null)) as missing_list,
       GROUP_CONCAT(IF(is_found = 1, name, null)) as found_list
FROM listing
GROUP BY town;

与 if 相比，我更喜欢 case (因为它是标准的，因此几乎在所有数据库中都可用):

SELECT town, 
       GROUP_CONCAT(CASE WHEN is_found = 0 THEN name END) as missing_list,
       GROUP_CONCAT(CASE WHEN is_found = 1 THEN name END) as found_list
FROM listing
GROUP BY town;