主页 / dba / 问题 / 207149
Accepted
Thunder Rabbit
Asked: 2018-05-18 22:51:52 +0800 CST

GROUP_CONCAT 不受 WHERE 限制

  • 772

Buildings并且与Facilities它们之间的 id 链接表具有多对多关系。我想选择具有特定设施的建筑物，并在单个列中显示每个建筑物的其他设施。

如果我 SELECT 基于 TABLE ，则使用 GROUP_CONCAT 有效Buildings，

SELECT `b`.`building_name`, `b`.`address`, 
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`

http://sqlfiddle.com/#!9/ceec20/10

但如果我还基于设施进行选择，则只会在 GROUP_CONCAT 中返回该设施。

SELECT `b`.`building_name`, `b`.`address`, 
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1 AND `facility_id` = 2
GROUP BY `building_id`

http://sqlfiddle.com/#!9/ceec20/11

如何返回包含每栋大楼所有设施的结果？

mysql group-concatenation

1 个回答

  1. Best Answer
    SELECT `b`.`building_name`, `b`.`address`, 
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`,
GROUP_CONCAT(CASE `facility_id` WHEN 2 THEN `ft`.`facility_type` END ORDER BY `facility_type`) AS `facilities2`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1
GROUP BY `building_id`;

    http://sqlfiddle.com/#!9/ceec20/13

    您是否知道在第三个查询中为设施 2 删除 NULL 行的方法？

    我们不能在 WHERE 子句中从输出列表中引用别名。要删除记录，facilities2 IS NULL我们必须在 HAVING 子句中使用适当的条件：

    SELECT `b`.`building_name`, `b`.`address`, 
GROUP_CONCAT(`ft`.`facility_type` ORDER BY `facility_type`) AS `facilities`,
GROUP_CONCAT(CASE `facility_id` WHEN 2 THEN `ft`.`facility_type` END) AS `facility2`
FROM `buildings` `b`
JOIN `buildings_2_facilities` USING (`building_id`)
JOIN `facility_types` `ft` USING (`facility_id`)
WHERE `b`.`latitude` > 1 
GROUP BY `building_id`
HAVING `facility2` IS NOT NULL;

    http://sqlfiddle.com/#!9/ceec20/17

    • 1

相关问题