具有连接操作的 MySQL group_concat() 函数

Question

我有下表:

CREATE TABLE school(
    schoolId VARCHAR(36),
    sname VARCHAR(64)
    PRIMARY KEY(schoolId)
);

CREATE TABLE school_building(
    schoolId VARCHAR(36),
    bname VARCHAR(64),
    PRIMARY KEY(schoolId, bname),
    FOREIGN KEY (schoolId) REFERENCES school(schoolId)
);

CREATE TABLE school_faculty(
    schoolId VARCHAR(36),
    fname VARCHAR(64),
    PRIMARY KEY(schoolId, fname),
    FOREIGN KEY (schoolId) REFERENCES school(schoolId)
);

我想编写一个查询来检索学校及其建筑物和院系作为逗号分隔的字段。当我只写以下内容时:

SELECT school.*, group_concat(bname SEPARATOR ',') as buildings, group_concat(fname SEPARATOR ',') as faculties 
FROM school S 
LEFT OUTER JOIN school_building B 
on S.schoolId = B.schoolId 
LEFT OUTER JOIN school_faculty F 
on S.schoolId = F.schoolId 
GROUP BY school.schoolId;

它给出了我想要的结果。但是，如果建筑物名称或教职工名称满足某些条件，我想编写一个查询来选择学校及其建筑物和教职工作为逗号分隔的字段。例如，如果(它有医学或工程系)或(它有医院或餐厅)建筑物，我想选择学校。为此，我写了以下内容:

SELECT school.*, group_concat(bname SEPARATOR ',') as buildings, group_concat(fname SEPARATOR ',') as faculties 
FROM school S 
LEFT OUTER JOIN school_building B 
on S.schoolId = B.schoolId 
LEFT OUTER JOIN school_faculty F 
on S.schoolId = F.schoolId 
WHERE B.name in ('hospital', 'restaurant') or F.name in ('medicine', 'engineering') 
GROUP BY school.schoolId;

但是，尽管此查询返回了正确的学校，但它带来了错误的建筑物或院系。例如，假设一所学校有三栋建筑物，名称分别为医院和旅馆。但是，在结果集中，学校只有医院。显然，where 子句以某种方式阻止了未在 where 子句中列出的建筑物被 group_concat() 逗号分隔。我该如何修复这个查询？

Answer 1

如果我理解您的要求，您希望在一所学校拥有所需建筑和教职工时收回所有建筑和教职工。

可能有几种方法可以做到这一点。您可以使用几个子查询来查找拥有必要建筑物或院系的学校，然后将其与主查询结合起来。

或者另一种方法是使用 FIND_IN_SET 和 HAVING 检查字段是否包含所需的建筑物或教员。

SELECT school.*, GROUP_CONCAT(DISTINCT bname SEPARATOR ',') AS buildings, GROUP_CONCAT(DISTINCT fname SEPARATOR ',') AS faculties 
FROM school S 
LEFT OUTER JOIN school_building B 
ON S.schoolId = B.schoolId 
LEFT OUTER JOIN school_faculty F 
ON S.schoolId = F.schoolId 
GROUP BY school.schoolId 
HAVING FIND_IN_SET('hospital', buildings)
OR FIND_IN_SET('restaurant', buildings)
OR FIND_IN_SET('engineering', faculties) 
OR FIND_IN_SET('medicine', faculties) 

编辑

使用 piotrms 建议:-

SELECT S.*, GROUP_CONCAT(DISTINCT B.bname SEPARATOR ',') AS buildings, GROUP_CONCAT(DISTINCT F.fname SEPARATOR ',') AS faculties 
FROM school S 
LEFT OUTER JOIN school_building B1 
ON S.schoolId = B1.schoolId 
AND B1.bname IN ('hospital', 'restaurant')
LEFT OUTER JOIN school_building B 
ON S.schoolId = B.schoolId 
LEFT OUTER JOIN school_faculty F1 
ON S.schoolId = F1.schoolId 
AND F1.fname IN ('engineering', 'medicine')
LEFT OUTER JOIN school_faculty F 
ON S.schoolId = F.schoolId 
WHERE B1.schoolId IS NOT NULL OR F1.schoolId IS NOT NULL
GROUP BY S.schoolId ;