sql集合思想的一个例子

2018-02-03

一次sql解决以下结果集

"sql集合结果"

问：有一个员工表emp 10000+数据emp_no=员工编号,dept_no=部门编号,job=null:其他,CLERK:员工,MANAGE:经理,sal=工资
"sql集合emp"

有一个部门表dept 50个条部门数据dept_no=部门编号,dept_name=部门名
"sql集合dept"

解决sql
1.emp表通过group by dept缩小范围并且按照各自的职位计算出各职位对应的工资和部门总工资

SELECT
       dept_no,
       SUM(IF(job='MANAGE',sal,0))AS manage,
       SUM(IF(job='CLERK',sal,0))AS cleark,
       SUM(IF(job IS NULL,sal,0))AS etc,
       SUM(sal)AS dept_sal
FROM emp
GROUP BY dept_no

2.缩小范围的emp和dept一一对应50找50

SELECT b.dept_name,a.dept_no,a.manage,a.cleark,a.etc,a.dept_sal
FROM (
    SELECT
           dept_no,
           SUM(IF(job='MANAGE',sal,0))AS manage,
           SUM(IF(job='CLERK',sal,0))AS cleark,
           SUM(IF(job IS NULL,sal,0))AS etc,
           SUM(sal)AS dept_sal
    FROM emp
    GROUP BY dept_no
) AS a,dept b
WHERE a.dept_no=b.dept_no

3.此时已经有了50条各部门数据但是差一条汇总数据,要用到笛卡尔积方法来实现汇总。

3.1.建立两张虚表50*2=100

3.2.按部门分组1为有部门,2为没部门

3.3.然后select的部分区分一下

SELECT
    IFNULL(IF(d.NO='1',c.dept_name,NULL),'汇总') AS dname,
    SUM(c.manage)AS manage,
    SUM(c.cleark)AS cleark,
    SUM(c.etc)AS etc,
    SUM(c.dept_sal)AS dept_sal
FROM (
    SELECT b.dept_name,a.dept_no,a.manage,a.cleark,a.etc,a.dept_sal
    FROM (
        SELECT
               dept_no,
               SUM(IF(job='MANAGE',sal,0))AS manage,
               SUM(IF(job='CLERK',sal,0))AS cleark,
               SUM(IF(job IS NULL,sal,0))AS etc,
               SUM(sal)AS dept_sal
        FROM emp
        GROUP BY dept_no
    ) AS a,dept b
    WHERE a.dept_no=b.dept_no
    AND b.dept_no>0
) c,
(SELECT '1' AS NO FROM DUAL
UNION ALL
SELECT '2' FROM DUAL
) AS d
GROUP BY IF(d.NO='1',c.dept_name,NULL)
ORDER BY d.NO,c.dept_no

3.4.执行计划
"sql集合explain"

3.5.执行计划结果说明参考下面这个人的博客
explain说明