根据雇员的家庭信息,统计雇员的孩子数量...
“用外连接进行行列转换(列→行):汇总重复项于一列”部分, 我们可以求得以员工为单位的员工子女列表。有了这个列表后,对员工进行一下聚合很容易就可以知道每个员工抚养了几个孩子。
输 出结果如下所示:
employee child_cnt
-------- --------
赤井 3
工藤 2
铃木 1
吉田 0
首先,创建Personnel表;
CREATE TABLE Personnel(
employee CHAR(50) NOT NULL,
child_1 CHAR(50),
child_2 CHAR(50),
child_3 CHAR(50)
);
插入雇员和孩子等家庭信息,查询信息;
SELECT * FROM Personnel;
使用union all形成新的雇员与孩子信息;
SELECT employee, child_1 AS child FROM Personnel
UNION ALL
SELECT employee, child_2 AS child FROM Personnel
UNION ALL
SELECT employee, child_3 AS child FROM Personnel;
在此新视图基础上,进行所需的查询统计;
SELECT
A.employee,COUNT(A.child)
FROM
(
SELECT employee, child_1 AS child FROM Personnel
UNION ALL
SELECT employee, child_2 AS child FROM Personnel
UNION ALL
SELECT employee, child_3 AS child FROM Personnel
) A
GROUP BY A.employee;
未完待续...