MySQL:组子句的多列

Question

我想显示每个部门每个角色的员 worker 数。为此，我创建了下面提到的查询。但它没有给出 (a) 所有角色 (b) 在员 worker 数为零的各个部门中担任角色的所有员工。

SELECT qodi.department_name,IFNULL(qobi.band_name, 'No Band') 'Band',count(qu1.user_id) emp_count
FROM 
qee_org_dept_info qodi
LEFT OUTER JOIN qee_user qu1 ON qodi.department_name =qu1.department AND qu1.org_id=1
LEFT OUTER JOIN qee_org_band_info qobi ON qobi.band_name = qu1.band_name
GROUP BY qodi.department_name,qobi.band_name
ORDER BY qodi.department_name

角色在 qee_org_band_info 中定义。用户在 qee_user 中定义，部门在 qee_org_dept_info 中定义

上述查询的结果是:-

预期结果是:- 所有部门和所有角色都应该来，即使相应部门中该角色的员 worker 数为零。

Answer 1

All departments and all roles should come, even if employee count for that role in respective department is zero.

这意味着，您需要在部门和角色之间生成笛卡尔连接，并根据笛卡尔连接的结果连接员工。

SELECT qodi.department_name,IFNULL(qobi.band_name, 'No Band') 'Band',count(qu1.user_id) emp_count
FROM 
(qee_org_dept_info qodi INNER JOIN qee_org_band_info qobi)
LEFT OUTER JOIN qee_user qu1 ON qodi.department_name =qu1.department AND qobi.band_name = qu1.band_name AND qu1.org_id=1 
GROUP BY qodi.department_name,qobi.band_name
ORDER BY qodi.department_name

请注意，我更改了连接的顺序，并且我使用内部连接而不是左连接来连接带有乐队的部门。根据 mysql 文档:

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

从技术上讲，内部连接表达式不需要括号，但是，我觉得这样代码可读性更好。