SQL语句,子查询数? [英] SQL statement, subquery count?
本文介绍了SQL语句,子查询数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下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
当然,这缺少'n男性雇员'。我被困在这里,因为我不确定在哪里指定附加子句e.gender = male。
That's missing the 'n of male employees', of course. I'm stuck here, since I'm not sure, where to specify the additional clause e.gender = male.
我忘了提及:HQL或标准是很好。
I forgot to mention: HQL or criteria would be nice.
推荐答案
仅供参考:
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中工作。谢谢大家!
works in HQL. Thanks everyone!
这篇关于SQL语句,子查询数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文