SQL语句,子查询数?
发布于 2021-01-30 16:38:07
我有以下SQL表
部
|name|employees|
雇员
|name|gender|type|dead |
|John|male |good|yes |
|Mary|female|bad |no |
|Joe |male |ugly|maybe|
我想写一条返回的声明
| type | n of employees | n of male employees | n of departments |
我有
SELECT e.type, count(e), count(d)
FROM Department d
JOIN d.employees e
WHERE e.dead = maybe
GROUP BY e.type
当然,这缺少“男性雇员的数量”。由于不确定,我被困在这里,在哪里指定附加子句e.gender = male。
我忘了提:HQL或标准会很好。
关注者
0
被浏览
72
1 个回答
-
假设原始查询和架构正确:
SELECT e.type, count(e), count(d), count (case when gender = 'male' then 1 else NULL end) AS NumberOfMaleEmployees from Department d JOIN d.employees e WHERE e.dead = 'maybe' GROUP BY e.type
-
仅供参考:
SELECT e.type, count(e), count(d), sum(case when gender = 'male' then 1 else 0 end) from Department d JOIN d.employees e WHERE e.dead = 'maybe' GROUP BY e.type
在HQL工作。感谢大家!