mysql查询获取id不同列的记录

Question

以下是我的表结构的详细信息。

表结构“部门”:

id  |   department
1   |   Department 1
2   |   Department 2
3   |   Department 3
4   |   Department 4

表结构“custom_forms_departments”:

id  |   form_id | department_id | enduser_to_department | department_to_enduser
1   |   5       |   1           |       Y               |       N
2   |   6       |   1           |       N               |       Y
3   |   8       |   2           |       Y               |       Y
4   |   7       |   3           |       N               |       Y
5   |   4       |   3           |       Y               |       N
6   |   2       |   4           |       N               |       N

结果应返回 department_id，其在同一行或不同行中的字段“enduser_to_department”和“department_to_enduser”的值为“Y”。

department_id = 1 contatin value "Y"for "enduser_to_department"and "department_to_enduser"in different rowsdepartment_id = 2 contatin value "Y"for "enduser_to_department"and "department_to_enduser"in the same rowsdepartment_id = 3 contatin value "Y"for "enduser_to_department"and "department_to_enduser"in different rows

结果:

  department_id | departments
    1           |   Department 1
    2           |   Department 2
    3           |   Deapartment 3

我正在使用以下未给出正确结果的 SQL 查询。

SELECT departments.department_id, departments.department 
FROM custom_forms_departments , departments
WHERE departments.department_id = custom_forms_departments.department_id
AND (custom_forms_departments.enduser_to_department = 'Y'
OR  custom_forms_departments.department_to_enduser = 'Y')
GROUP BY departments.department_id 
ORDER BY departments.department_id DESC

请给我推荐这个。

Answer 1

首先尝试处理custom_forms_departments。

方法:创建 2 个 custom_forms_departments 副本(c1 和 c2)。你会想加入他们，基于 (c1.department_id = c2.department_id) - 简单，并且 (c1.enduser_to_department = c2.department_to_enduser) - 因为你想要只获取同时包含“Y”的行(将过滤 WHEN 中的“Y”，但现在，您将获得在两列中具有相同值的任何行)。其次，使用 WHEN 仅过滤“Y”。

SELECT 
    custom_forms_departments.department_id
FROM
    custom_forms_departments c1
        INNER JOIN
    custom_forms_departments c2 ON c1.department_id = c2.department_id
        AND c1.enduser_to_department = c2.department_to_enduser
WHERE
    c1.enduser_to_department = 'Y'
GROUP BY
    c1.department_id 
;

现在我们有了“复杂”的员工，让我们把所有人员聚集在一起并添加departments 列:

SELECT 
    departments.department_id, departments.department
FROM
    departments
        INNER JOIN
    (SELECT 
        custom_forms_departments.department_id
    FROM
        custom_forms_departments c1
    INNER JOIN custom_forms_departments c2 ON c1.department_id = c2.department_id
        AND c1.enduser_to_department = c2.department_to_enduser
    WHERE
        c1.enduser_to_department = 'Y'
    GROUP BY
        c1.department_id) c3 ON departments.department_id = c3.department_id
;