以下是我的表结构的详细信息。
表结构“部门”：
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在不同行中的“ enduser_to_department”和“ department_to_enduser”的连续值“ Y”对于同一行中“ enduser_to_department”和“ department_to_enduser”的连续值“ Y”在“ enduser_to_department”中的连续值“ Y”和“ department_to_enduser”在不同的行中
结果：
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
请为我推荐这个。
首先尝试照顾custom_forms_departments。
方式：创建2个副本custom_forms_departments（c1和c2）。您将希望基于（c1.department_id = c2.department_id）-simple和（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
;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句