请教这个SQL怎么写 - 墨天轮问答

请教这个SQL怎么写

我来答

2022-11-28

请教这个SQL怎么写

表Table_A有四个字段detp_type,dept_code,employee_type,count分别代表部门类型，部门代码，员工类型，人数，下图白色部分是表里已经有了的数据（SQL已经查到这一步），我想按dept_type来求和，A,B两个类型的Manager,Staff,Operator分别有多少人，要在已有的数据加上黄色的那几条记录，rollup好像不行，怎么写？谢谢

另外，group_id,grouping,group_id(exp...) 到底怎么个用法？

我来答

添加附件

问题补充

3条回答

默认

最新

x1e

SELECT *
  FROM (
      SELECT 'A' AS dept_type, 'D1' AS dept_code, 'M' AS employee_type, 2 AS num
        FROM dual
       UNION ALL
      SELECT 'A' AS dept_type, 'D1' AS dept_code, 'S' AS employee_type, 10 AS num
        FROM dual
       UNION ALL
      SELECT 'A' AS dept_type, 'D1' AS dept_code, 'O' AS employee_type, 20 AS num
        FROM dual
       UNION ALL
      SELECT 'A' AS dept_type, 'D2' AS dept_code, 'M' AS employee_type, 1 AS num
        FROM dual
       UNION ALL
      SELECT 'A' AS dept_type, 'D2' AS dept_code, 'S' AS employee_type, 5 AS num
        FROM dual
       UNION ALL
      SELECT 'A' AS dept_type, 'D2' AS dept_code, 'O' AS employee_type, 10 AS num
        FROM dual
       UNION ALL
      SELECT 'B' AS dept_type, 'D1' AS dept_code, 'M' AS employee_type, 0 AS num
        FROM dual
       UNION ALL
      SELECT 'B' AS dept_type, 'D1' AS dept_code, 'S' AS employee_type, 5 AS num
        FROM dual
       UNION ALL
      SELECT 'B' AS dept_type, 'D1' AS dept_code, 'O' AS employee_type, 10 AS num
        FROM dual
  )

union ALL

SELECT  dept_type, null dept_code, employee_type,sum(num)
  FROM (
      SELECT 'A' AS dept_type, 'D1' AS dept_code, 'M' AS employee_type, 2 AS num
        FROM dual
       UNION ALL
      SELECT 'A' AS dept_type, 'D1' AS dept_code, 'S' AS employee_type, 10 AS num
        FROM dual
       UNION ALL
      SELECT 'A' AS dept_type, 'D1' AS dept_code, 'O' AS employee_type, 20 AS num
        FROM dual
       UNION ALL
      SELECT 'A' AS dept_type, 'D2' AS dept_code, 'M' AS employee_type, 1 AS num
        FROM dual
       UNION ALL
      SELECT 'A' AS dept_type, 'D2' AS dept_code, 'S' AS employee_type, 5 AS num
        FROM dual
       UNION ALL
      SELECT 'A' AS dept_type, 'D2' AS dept_code, 'O' AS employee_type, 10 AS num
        FROM dual
       UNION ALL
      SELECT 'B' AS dept_type, 'D1' AS dept_code, 'M' AS employee_type, 0 AS num
        FROM dual
       UNION ALL
      SELECT 'B' AS dept_type, 'D1' AS dept_code, 'S' AS employee_type, 5 AS num
        FROM dual
       UNION ALL
      SELECT 'B' AS dept_type, 'D1' AS dept_code, 'O' AS employee_type, 10 AS num
        FROM dual
  )
 GROUP BY ROLLUP (dept_type, employee_type)
HAVING grouping(employee_type) <> 1
;

有用 1

打赏 0

游湖

2022-11-28

写的挺长

布衣

2022-11-28

Thomas

用grouping sets:

with t as
(SELECT 'A' AS dept_type, 'D1' AS dept_code, 'M' AS employee_type, 2 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D1' AS dept_code, 'S' AS employee_type, 10 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D1' AS dept_code, 'O' AS employee_type, 20 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D2' AS dept_code, 'M' AS employee_type, 1 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D2' AS dept_code, 'S' AS employee_type, 5 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D2' AS dept_code, 'O' AS employee_type, 10 AS num
FROM dual
UNION ALL
SELECT 'B' AS dept_type, 'D1' AS dept_code, 'M' AS employee_type, 0 AS num
FROM dual
UNION ALL
SELECT 'B' AS dept_type, 'D1' AS dept_code, 'S' AS employee_type, 5 AS num
FROM dual
UNION ALL
SELECT 'B' AS dept_type, 'D1' AS dept_code, 'O' AS employee_type, 10 AS num
FROM dual)
select * from t
union all
select * from (select dept_type,null,employee_type,sum(num) from t group by grouping sets((dept_type,employee_type)) HAVING grouping_id(dept_type,employee_type) = 0 order by 1,3);

用rollup:

with t as
(SELECT 'A' AS dept_type, 'D1' AS dept_code, 'M' AS employee_type, 2 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D1' AS dept_code, 'S' AS employee_type, 10 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D1' AS dept_code, 'O' AS employee_type, 20 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D2' AS dept_code, 'M' AS employee_type, 1 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D2' AS dept_code, 'S' AS employee_type, 5 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D2' AS dept_code, 'O' AS employee_type, 10 AS num
FROM dual
UNION ALL
SELECT 'B' AS dept_type, 'D1' AS dept_code, 'M' AS employee_type, 0 AS num
FROM dual
UNION ALL
SELECT 'B' AS dept_type, 'D1' AS dept_code, 'S' AS employee_type, 5 AS num
FROM dual
UNION ALL
SELECT 'B' AS dept_type, 'D1' AS dept_code, 'O' AS employee_type, 10 AS num
FROM dual)
select * from t
union all
select dept_type,null,employee_type,sum(num) from t group by rollup((dept_type,employee_type)) HAVING grouping(employee_type) <> 1 ;

用cube:

with t as
(SELECT 'A' AS dept_type, 'D1' AS dept_code, 'M' AS employee_type, 2 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D1' AS dept_code, 'S' AS employee_type, 10 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D1' AS dept_code, 'O' AS employee_type, 20 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D2' AS dept_code, 'M' AS employee_type, 1 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D2' AS dept_code, 'S' AS employee_type, 5 AS num
FROM dual
UNION ALL
SELECT 'A' AS dept_type, 'D2' AS dept_code, 'O' AS employee_type, 10 AS num
FROM dual
UNION ALL
SELECT 'B' AS dept_type, 'D1' AS dept_code, 'M' AS employee_type, 0 AS num
FROM dual
UNION ALL
SELECT 'B' AS dept_type, 'D1' AS dept_code, 'S' AS employee_type, 5 AS num
FROM dual
UNION ALL
SELECT 'B' AS dept_type, 'D1' AS dept_code, 'O' AS employee_type, 10 AS num
FROM dual)
select * from t
union all
select * from (select dept_type,null,employee_type,sum(num) from t group by cube(dept_type,employee_type) HAVING grouping_id(dept_type,employee_type) = 0 order by 1,3);

有用 0

打赏 0

刘晓华

题主

2022-11-30

厉害！grouping,group_id,grouping sets这几个东东之前还没用过，正好有这么个需求学习一下，受教了

游湖

学习了

有用 1

打赏 0

回答交流

提交

问题信息

请登录之后查看

邀请回答

暂无人订阅该标签，敬请期待~~

墨值悬赏