使用Mysql查询获取id对应的不同列的记录

Question

以下是我的表结构细节。

表结构 "departments":

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

返回结果应该是包含字段"enduser_to_department"和"department_to_enduser"值都为"Y"的同一行或不同行的部门ID。

例如：

部门ID = 1，"enduser_to_department"和"department_to_enduser"两个字段在不同行上都有值"Y"

部门ID = 2，"enduser_to_department"和"department_to_enduser"两个字段在同一行上都有值"Y"

部门ID = 3，"enduser_to_department"和"department_to_enduser"两个字段在不同行上都有值"Y"

结果:

  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

请为这个提供建议。

- pratik

---

你可能想以更美观的方式格式化该内容。 - pala_

Answer 1

首先尝试处理custom_forms_departments。

方法：创建custom_forms_departments的2个副本（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 
;

现在我们已经有了“复杂”的员工，让我们把所有员工聚集在一起并添加“部门”列：

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
;

- Captain Crunch

---

感谢Captain Crunch :) - pratik

我还想将它与部门表连接起来，以获取部门名称。您能否更新查询以获得精确结果。拜托了。。 - pratik

@patrik：已添加该部分，请查看答案。 - Captain Crunch

加一分给正确答案！ - Pratik Joshi

@jQuery.PHP：谢谢。@patrik：请注意，我在WHERE中只使用了一个条件和一个GROUP BY（我想departments.department_id是您的主键，所以不需要对其进行分组），这更有效率。此外，在“内部查询”中连接和过滤会留下较少的连接用于外部查询（这就是我使用内部查询的原因）。 - Captain Crunch

Answer 2

请使用以下查询。同时始终使用明确的 JOIN。

SELECT d.department_id, d.department 
FROM custom_forms_departments AS cfd
INNER JOIN departments AS d ON d.department_id = cfd.department_id
AND (cfd.enduser_to_department = 'Y' OR cfd.department_to_enduser = 'Y')
GROUP BY d.department_id, d.department 
ORDER BY d.department_id

- sqluser

Answer 3

SELECT id as department_id, department from departments WHERE id IN 
(
SELECT DISTINCT(departament_id) FROM custom_forms_departments WHERE enduser_to_department = 'Y' OR department_to_enduser = 'Y'
)

- jparaya