一个SQL练习题

有2张表，为父子关系表：

1 Employee 表
   id         Name         Age
   1         张三            31
   2         李四            28
   3         王五            40
   ...

2 Wages 表
-------------------------------------------------------------
Emp_id                 | 工资项目         | 工资金额         |
-------------------------------------------------------------
1                           | 基本工资         | 1.00                 |
-------------------------------------------------------------
1                           | 补助               | 2.00                 |
-------------------------------------------------------------
1                           | 津贴               | 3.00                 |
-------------------------------------------------------------
2                           | 基本工资         | 4.00                 |
-------------------------------------------------------------
2                           | 补助                | 5.00                 |
-------------------------------------------------------------
2                           | 津贴                | 6.00                 |
-------------------------------------------------------------
3                           | 基本工资         | 7.00                 |
-------------------------------------------------------------
3                           | 补助                | 8.00                 |
-------------------------------------------------------------
3                           | 津贴                | 9.00                 |
-------------------------------------------------------------
.........

其中， Employee 表的 id 对应于 Wages 表的 Emp_id 是1对多的关系。


不用存储过程，能否直接用查询检索出下面的结果？

   id         Name         Age         基本工资         补助         津贴         合计
   1         张三            31                1                 2               3             6
   2         李四            28                4                 5               6            15
   3         王五            40                7                 8               9            24

--------------------------------------------------------------------------------------------------------------------------
SELECT ID, NAME, age, t1.基本工资,t2.补助,t3.津贴,t4.合计
   FROM employee,
        (SELECT emp_id, 工资金额 AS 基本工资
           FROM wages
          WHERE 工资项目 = '基本工资') t1,
        (SELECT emp_id, 工资金额 AS 补助
           FROM wages
          WHERE 工资项目 = '补助') t2,
        (SELECT emp_id, 工资金额 AS 津贴
           FROM wages
          WHERE 工资项目 = '津贴') t3,
        (SELECT    emp_id, SUM (工资金额) AS 合计
             FROM wages
         GROUP BY emp_id) t4
WHERE ID = t1.emp_id AND ID = t2.emp_id AND ID = t3.emp_id AND ID = t4.emp_id

转载于:https://www.cnblogs.com/A2008A/articles/1423742.html