我有以下查询...
SELECT COUNT(Department) as DeptCount, Survey.Department, Department.DepartmentName
FROM Survey JOIN Department ON Survey.Department = Department.DeptID
Where SurveyCompleted is not null GROUP BY DEPARTMENT ORDER BY Department;
唯一的问题是我依靠的字段"部门"并不总是输入值(出于多种原因)。我可以使用另一个领域的员工。departmentID,但是如果我使用内联或类似构造,我不确定如何创建相同的效果。
本质上,我想看到的是输出是:
DeptCount Department DepartmentName
43 2 Department1
68 9 Department2
83 13 Department3
现在,我得到了一个额外的行,作为第一个结果
DeptCount Department DepartmentName
93 0 <blank>
43 2 Department1
68 9 Department2
83 13 Department3
任何帮助都将不胜感激。
谢谢。
如果您尝试此操作会怎样?通过使加入左加入,我们从Survey
获得所有记录以及Department
中的任何匹配记录。然后我们可以汇总它们。
SELECT COUNT(Department) as DeptCount, coalesce(Survey.Department, 0)
, Department.DepartmentName
FROM Survey
left JOIN Department ON Survey.Department = Department.DeptID
Where SurveyCompleted is not null
GROUP BY DEPARTMENT ORDER BY Department;
顺便说一句,group by
自动订购,因此,如果要分组,则不需要order by
。
尝试此
SELECT COUNT(Department) as DeptCount, Survey.Department, Department.DepartmentName
FROM Survey JOIN Department ON Survey.Department = Department.DeptID
Where SurveyCompleted is not null and Survey.Department is not null and Survey.Department <> ''
GROUP BY DEPARTMENT
ORDER BY Department;
看来我试图使它过于复杂。
我要做的就是命名从条件操作员创建的结果。然后,我可以按照该值进行分组...并且它起作用(或似乎是)。
这是我的结果查询:
SELECT COUNT(Department) as DeptCount,
IF(Survey.Department=0, Employee.DepartmentId, Survey.Department) As MyDept,
Department.DepartmentName
FROM Survey JOIN Department ON Survey.Department = Department.DeptID
JOIN Employee ON EmployeeId = Employee.Id
WHERE SurveyCompleted is not null
GROUP BY MyDept;