关联查询SQL题

题目：

      已知工资表sal包含3个字段：eid（员工编号），year（年份），sal（当年工资）

     eid                   year                          sal             

      1                     2001                      1000

      1                     2002                      1200

      1                     2003                      2800

      2                     2001                      1200

      2                     2002                      1200

 

 

(1)计算每位员工各年的累计工资(eid,year,total_sal)

    select a.eid,a.year,sum(b.sal) as total_sal  from sal a,sal b  where a.eid=b.eid and a.year>=b.year group by a.eid,a,year order by a.eid,a.year

 

运行结果为：

   eid                   year                         total_ sal             

      1                     2001                      1000

      1                     2002                      2200

      1                     2003                      5000

      2                     2001                      1200

      2                     2002                      2400

 

(2)因业务需求表中增加了tax字段，计算所得税，年薪>=2000所得税为10%，小于2000者补计算。

    更新表结构

 update sal a set tax(select a.sal*0.1 from sal b where a.eid=b.eid and a.year =b .year and a.sal >=2000)

 

运行结果：

   eid                   year                         total_ sal     tax         

      1                     2001                      1000           null

      1                     2002                      2200           220

      1                     2003                      5000           500

      2                     2001                      1200           null

      2                     2002                      2400           240