复杂的Oracle的查询--查询各个部门的总员工和总薪水占整个公司的比率

查询各个部门的总员工和总薪水占整个公司的比率:

 

SELECT a.department_id "Department",
   a.num_emp/b.total_count "%_Employees",
   a.sal_sum/b.total_sal "%_Salary"
FROM
(SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum
   FROM employees
   GROUP BY department_id) a,
(SELECT COUNT(*) total_count, SUM(salary) total_sal
   FROM employees) b;

 

[b][color=red]a:[/color][/b]

SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum
   FROM employees
   GROUP BY department_id

 [b][color=red]b:[/color][/b]

SELECT COUNT(*) total_count, SUM(salary) total_sal
   FROM employees

 [b][color=red]c:[/color][/b]

SELECT a.department_id "Department",
   a.num_emp/b.total_count "%_Employees",
   a.sal_sum/b.total_sal "%_Salary"
FROM a, b

 这样解析起来：解构比较清晰

[b]总结：在Oracle中的查询 无非是不断的创建一个temporary Table 然后在查询[/b]