根据表一和表二写出查询结果如表三的sql语句

table1  
月份mon 部门dep 业绩yj
-------------------------------         建表：create table sale(mon char(3),depId char(2),yj char(2));
一月份      01      10                  添加数据：insert into sale values('一月份','01','10');
一月份      02      10               insert into sale values('一月份','02','10');
一月份      03      05               insert into sale values('一月份','03','05');
二月份      02      08                            insert into sale values('二月份','02','08');
二月份      04      09               insert into sale values('二月份 ','04','09');
三月份      03      08               insert into sale values('三月份','03','08');

table2
部门dep      部门名称dname              建表：create table department(depId char(2),depName char(6));
--------------------------------
      01      国内业务一部                    insert into department values('01','国内业务一部');
      02      国内业务二部                    insert into department values('02','国内业务二部');
      03      国内业务三部                    insert into department values('03','国内业务三部');
      04      国际业务部                        insert into department values('04','国际业务部');

table3 （result）
部门dep              一月份      二月份      三月份
--------------------------------------
      国内业务一部      10        null        null
      国内业务二部      10         08         null
      国内业务三部      05       null         08
      国际业务部        null      09         null

 

要求：根据表一和表二写出查询结果如表三的sql语句。

 

解决方案一：只是查询出结果

        select department.depName,(select sale.yj from sale where sale.mon="一月份" and department.depId=sale.depId) as "一月份" ,(select sale.yj from sale where  

        sale.mon="二月份" and department.depId=sale.depId) as "二月份" ,(select sale.yj from sale where sale.mon="三月份" and department.depId=sale.depId) as "三月

        份"  from department;

 

解决方案二：将查询结果存放在表result中，result表临时创建：

        create table result (select department.depName,(select sale.yj from sale where sale.mon="一月份" and department.depId=sale.depId) as "一月份" ,(select sale.yj 

         from sale where sale.mon="二月份" and department.depId=sale.depId) as "二月份" ,(select sale.yj from sale where sale.mon="三月份" and

         department.depId=sale.depId) as "三月份"  from department);

 

以下是执行过程贴图：