如何引用 1:N 关系链中的多个列

Question

我仍然处于 grails 的学习曲线中，并且遇到了以下问题：

class Departement {
String  name    // "hr", "it"...

static hasMany = [bureaus:Office]
}


class Office {
String  bureaunumber    // 102, 104, 106...

static hasMany = [ppl:Personnel]
}


class Personnel {
String  name    // "Smith", "Walker"...
String  title   // "project manager", "ceo", "financial manager"...
Integer salary
}

目标是找到特定部门中所有经理的工资总和。结果必须提供工资总额、经理总数和部门名称，如下所示：

|dept    |total manager|wages   |
|--------|-------------|--------|
| hr     | 4           | 340000 |
| it     | 7           | 610400 |
| ...    | ...         | ...    |
| all    | 11          | 950400 |

等。

我想使用 hibernate criteria api 创建一个结果集，但我坚持使用将提供的 SQL 查询从上面的结果。目前，它给了我适当的薪水：

select sum(salary) wages
from personnel
where title LIKE '%manager%'
  and office_id in
  (
    select office.id
    from office
    where office.dept_id in
    (
      select dept.id
      from dept
      where name = 'hr'
    )
  )

嗯 - 不幸的是到目前为止就这些了。有谁知道如何计算每个部门的经理总数以及如何将部门名称添加到结果集中？

任何意见将不胜感激！

=========================

大家好， 感谢您花时间回答我的问题！

我对 Ben 建议的解决方案中的 count(managers) 部分感到有点困惑，我在人事领域类中没有经理字段......我是否错过了那里的东西？

尝试使用 count(*) 和 count(personnel.name) ，但它给了我所有员工每个部门的工资总额，而且，它还累加了人员总数 - 而不仅仅是那些“经理” " title :-/

所以运行查询后的结果如下所示：

|dept    |total manager|wages    |
|--------|-------------|---------|
| hr     | 139         | 3988800 |
| it     | 139         | 3988800 |
| ...    | 139         | 3988800 |
| all    | 139         | 3988800 |

======== 更新 2: sql server 2005+ 到 oracle 的语法 ===========

SELECT
   CASE GROUPING(d.name)
     WHEN 1 THEN 'All:'
     ELSE d.name
   END dept,
   COUNT(*) total_manager,
   SUM(p.salary) wages
FROM departement d
   INNER JOIN office o ON d.id = o.dept_id
   INNER JOIN personnel p ON o.id = p.office_id
WHERE p.name LIKE '%manager%'
GROUP BY ROLLUP(d.name)

Answer 1

在 SQL Server 2005+ 中

SELECT
  CASE GROUPING(d.name)
    WHEN 1 THEN 'All:'
    ELSE d.name
  END AS dept,
  COUNT(*) AS total_manager,
  SUM(p.salary) AS wages
FROM dept d
  INNER JOIN office o ON d.id = o.dept_id
  INNER JOIN personnel p ON o.id = p.office_id
GROUP BY d.name WITH ROLLUP

Answer 2

我认为你很接近。使用 SUM 和 COUNT 时，您有时需要在 SQL 中使用 GROUP BY 子句，所以也许这就是您所缺少的。像这样的东西：

SELECT  dept,
        COUNT(managers),
        sum(salary) wages
FROM personal
WHERE title LIKE '%manager%'
AND office_id IN 
    (select office.id from office where office.dept_id in (select dept.id from dept where name = 'hr'))
GROUP BY managers