【SQL解惑】谜题15：找出最近两次工资

解惑一：

1、创建表和插入数据

create  table  Salaries

(

emp_name  char ( 10 )  not  null,

sal_date  date  not  null,

sal_amt  decimal  ( 8 , 2 )  not  null,

primary  key  ( emp_name , sal_date )

)

insert  into  Salaries

values  ( 'Tom' , '1996-06-20' , 500.00 ),

          ( 'Tom' , '1996-08-20' , 700.00 ),

          ( 'Tom' , '1996-10-20' , 800.00 ),

          ( 'Tom' , '1996-12-20' , 900.00 ),

          ( 'Dick' , '1996-06-20' , 500.00 ),

          ( 'Harry' , '1996-07-20' , 500.00 ),

          ( 'Harry' , '1996-09-20' , 700.00 )

go

2、创建视图

create  view  Salaries2 ( emp_name , sal_date , sal_amt )

as  select  s0 . emp_name , s0 . sal_date , MAX ( s0 . sal_amt )

        from  Salaries  as  s0 , Salaries  as  s1

       where  s0 . sal_date  <=  s1 . sal_date

         and  s0 . emp_name  =  s1 . emp_name

       group  by  s0 . emp_name , s0 . sal_date

    having  COUNT (*)  <=  2

go

解惑二：

select  s0 . emp_name , s0 . sal_date , s0 . sal_amt , s1 . sal_date , s1 . sal_amt

   from  Salaries  as  s0 , Salaries  as  s1

  where  s0 . emp_name  =  s1 . emp_name

    and  s0 . sal_date  =  ( select  MAX ( s2 . sal_date )

                                     from  Salaries  as  s2

                                  where  s0 . emp_name  =  s2 . emp_name )

    and  s1 . sal_date  =  ( select  MAX ( s3 . sal_date )

                                     from  Salaries  as  s3

                                  where  s0 . emp_name  =  s3 . emp_name

                                    and  s3 . sal_date  <  s0 . sal_date )

union  all

select  s4 . emp_name , max ( s4 . sal_date ), MAX ( s4 . sal_amt ),null,null

   from  Salaries  as  s4

  group  by  s4 . emp_name

having  COUNT (*)  =  1

解惑三：

1、语句块一（获取表的每个人的最大日期）：

（1）from子句获取Salaries表数据；

（2）groupby子句将表数据按emp_name分组

（3）select子句选择emp_name和最大的sal_date

select  w . emp_name , MAX ( w . sal_date )  as  maxdate

                           from  Salaries  as  w

                          group  by  w . emp_name

2、语句块二（获取表的每个人的最大日期和第二大日期）：

（1）from子句获取上面语句块一的数据；

（2）left outer join子句左外连接表Salaries，当表a的emp_name与表x的emp_name且表a的日期（即最大日期）小于表x的日期时就添加到表a中；

（3）groupby子句将数据按emp_name和maxdate进行分组；

（4）select语句选择表a的emp_name，表a的maxdate（即最大的日期），表x的最大日期（即除了最大日期之外的第二大日期）

select  a . emp_name , a . maxdate , max ( x . sal_date )  as  maxdate2

   from  a

   left  outer  join  Salaries  as  x

     on  a . emp_name  =  x . emp_name  and  a . maxdate  >  x . sal_date

  group  by  a . emp_name , a . maxdate

3、语句块三：

（1）from子句获取上面语句块二的数据；

（2）left outer join子句左外连接表Salaries，当表y与表z的emp_name和sal_date相等时则左外连接。

（3）select子句获取名字、最近日期、最近日期的工资、最近第二次的日期、最近第二次日期的工资

select  b . emp_name , b . maxdate , y . sal_amt , b . maxdate2 , z . sal_amt

   from  b

   left  outer  join  Salaries  as  y

     on  b . emp_name  =  y . emp_name  and  b . maxdate    =  y . sal_date

   left  outer  join  Salaries  as  z

     on  b . emp_name  =  z . emp_name  and  b . maxdate2  =  z . sal_date

4、完整语句

select  b . emp_name , b . maxdate , y . sal_amt , b . maxdate2 , z . sal_amt

   from  ( select  a . emp_name , a . maxdate , max ( x . sal_date )  as  maxdate2

               from  ( select  w . emp_name , MAX ( w . sal_date )  as  maxdate

                           from  Salaries  as  w

                          group  by  w . emp_name )  as  a

             left  outer  join  Salaries  as  x

             on  a . emp_name  =  x . emp_name  and  a . maxdate  >  x . sal_date

          group  by  a . emp_name , a . maxdate )  as  b

   left  outer  join  Salaries  as  y

     on  b . emp_name  =  y . emp_name  and  b . maxdate    =  y . sal_date

   left  outer  join  Salaries  as  z

     on  b . emp_name  =  z . emp_name  and  b . maxdate2  =  z . sal_date

解惑四：

1、使用解惑一的视图，通过日期的比较，只有前一个日期小于或等于后一个日期中的统计次数为小于等于2的即为前两个数。

例如：

日期为2017.1.1，2017.1.2，2017.1.3，2017.1.4。

那么按照公式的要求，自联结后日期进行比较并分组，那么存在如下分组。

2017.1.1<=2017.1.1	2017.1.2<=2017.1.2	2017.1.3<=2017.1.3	2017.1.4<=2017.1.4
2017.1.1<=2017.1.2	2017.1.2<=2017.1.3	2017.1.3<=2017.1.4
2017.1.1<=2017.1.3	2017.1.2<=2017.1.4
2017.1.1<=2017.1.4

通过上面的比对可了解到，只要分组成员少于或等于2的，那么对应的就是最近的两天

create  view  Salaries2 ( emp_name , sal_date , sal_amt )

as  select  s0 . emp_name , s0 . sal_date , MAX ( s0 . sal_amt )

        from  Salaries  as  s0 , Salaries  as  s1

       where  s0 . sal_date  <=  s1 . sal_date

         and  s0 . emp_name  =  s1 . emp_name

       group  by  s0 . emp_name , s0 . sal_date

    having  COUNT (*)  <=  2

2、选择数据

（1）选择其中的最大的数据。

select  s1 . emp_name , s1 . sal_date , s1 . sal_amt , s2 . sal_date , s2 . sal_amt

   from  Salaries2  as  s1 , Salaries2  as  s2

  where  s1 . emp_name  =  s2 . emp_name

    and  s1 . sal_date  >  s2 . sal_date

（2）选择其中数据量为1次的数据，即只有一天的数据。

select  emp_name , MAX ( sal_date ), MAX ( sal_amt ),null,null

   from  Salaries2

  group  by  emp_name

having  COUNT (*)  =  1

（3）将两种数据合并

select  s1 . emp_name , s1 . sal_date , s1 . sal_amt , s2 . sal_date , s2 . sal_amt

   from  Salaries2  as  s1 , Salaries2  as  s2

  where  s1 . emp_name  =  s2 . emp_name

    and  s1 . sal_date  >  s2 . sal_date

  union  all

select  emp_name , MAX ( sal_date ), MAX ( sal_amt ),null,null

   from  Salaries2

  group  by  emp_name

having  COUNT (*)  =  1

解惑五：

1、语句块一：

（1）from子句获取表Salaries为表1的数据；

（2）innerjoin表Salaries为表2的数据，根据两表的emp_name和表2的sal_date进行自联接；

（3）当表2的sal_date数据为获取表Salaries为表4的数据，且表4的emp_name和表1的emp_name相等的时候，表4的sal_date都小于表1的每一条sal_date的时候，当上一步的数据为空值时，则直接获取表2的sal_date即可。

以下为具体例子：

（1）左边为表1和表2内连接的结果，右边为表4的数据；

（2）第一个，当s1_sal_date的数据1996-6-20与表4的所有数据进行比较，都是小于或大于表4的数据，因此没有满足的数据可以返回，因此取本身这条数据的s2_sal_date；

（3）第二个，当s1_sal_date的数据1996-10-20与表4的所有数据进行比较，存在数据1996-10-20大于1996-6-20和1996-8-20，因此再这两个数据中取最大值，选择对应的s2_sal_date即可。

总结：以下的语句是可以查找出，每一行日期与之邻近的上一个日期

select  (...)

   from  Salaries  as  s1

  inner  join  Salaries  as  s2

     on  s2 . emp_name  =  s1 . emp_name

    and  s2 . sal_date  =  coalesce (( select  MAX ( s4 . sal_date )

                                from  Salaries  as  s4

                               where  s4 . emp_name  =  s1 . emp_name

                                  and  s4 . sal_date  <  s1 . sal_date ), s2 . sal_date )

2、语句块二：

（1）以语句块一的数据为基础；

（2）where子句中的子查询筛选数据：当表1的数据与表3的数据中的emp_name相等时时，如果表3的每一个sal_date日期都大于表1的sal_date日期时，则选择数据；

（3）当子查询没有任何数据时，才返回对应的sal_date。

当表1的1996-6-20与表3的所有sal_date比较时，存在1996-8-20、1996-10-20、1996-12-20大于表1的数据，因此在exists子查询中会返回数据；

当表1的1996-12-20与表3的所有sal_date比较时，不存在任何数据大于表1的数据，因此在exists子查询中不会返回任何数据；

同时where子句中的是not exists(true)时，则不返回数据；同时where子句中的是not exists(false)时，则返回数据。

select  s1 . emp_name  as  s1_emp_name ,

          s1 . sal_date  as  s1_sal_date ,

          s1 . sal_amt  as  s1_sal_amt ,

          s2 . emp_name  as  s2_emp_name ,

          s2 . sal_date  as  s2_sal_date ,

          s2 . sal_amt  as  s2_sal_amt

   from  Salaries  as  s1

  inner  join  Salaries  as  s2

     on  (...)

  where  not  exists( select  *

                     from  Salaries  as  s3

                    where  s3 . emp_name  =  s1 . emp_name

                      and  s3 . sal_date  >  s1 . sal_date )  

3、语句块三

（1）select子句选择表1的emp_name、sal_date、sal_amt

（2）如果表1的数据与表2的数据不相同时，则直接选择表2的数据；

如果表1的数据与表2的数据相同时，则证明该行数据就只有1个日期而已，则直接为空即可。

select  s1 . emp_name , s1 . sal_date  as  curr_date , s1 . sal_amt  as  curr_amt ,

          case  when  s2 . sal_date  <>  s1 . sal_date  then  s2 . sal_date  end  as  prev_date ,

          case  when  s2 . sal_date  <>  s1 . sal_date  then  s2 . sal_amt  end  as  prev_amt

   from  Salaries  as  s1

  inner  join  Salaries  as  s2

     on  s2 . emp_name  =  s1 . emp_name

    and  s2 . sal_date  =  coalesce (( select  MAX ( s4 . sal_date )

                                from  Salaries  as  s4

                               where  s4 . emp_name  =  s1 . emp_name

                                 and  s4 . sal_date  <  s1 . sal_date ), s2 . sal_date )

  where  not  exists( select  *

                    from  Salaries  as  s3

                   where  s3 . emp_name  =  s1 . emp_name

                     and  s3 . sal_date  >  s1 . sal_date )        

解惑六：

1、创建视图

（1）from子句获取表Salaries的数据

（2）leftouterjoin表Salaries，当表1的emp_name与表2的emp_name相同时，且表1的日期大于表2的日期。相当于获取所有日期与所有比该日期小的集合，如果没有则为空。

create  view  SalaryHistory ( emp_name , curr_date , curr_amt , prev_date , prev_amt )

as

select  s0 . emp_name , s0 . sal_date  as  curr_date ,

                            s0 . sal_amt  as  curr_amt ,

                            s1 . sal_date  as  prev_date ,

                            s1 . sal_amt  as  prev_amt

   from  Salaries  as  s0

   left  outer  join  Salaries  as  s1  on  s0 . emp_name  =  s1 . emp_name

   and  s0 . sal_date  >  s1 . sal_date

go

2、查询数据

（1）from子句获取数据

（2）where子句筛选表1的最大日期，表1代表所有日期的最大日期，表2代表所有日期的邻近日期的最大日期。筛选出来的日期即为表的最大日期和最大的邻近日期。

select  s0 . emp_name ,

          s0 . curr_date ,

          s0 . curr_amt ,

          s0 . prev_date ,

          s0 . prev_amt

   from  SalaryHistory  as  s0

  where  s0 . curr_date  =  ( select  MAX ( curr_date )

                                      from  SalaryHistory  as  s1

                                     where  s0 . emp_name  =  s1 . emp_name )

    and  s0 . prev_date  =    ( select  MAX ( prev_date )

                                        from  SalaryHistory  as  s2

                                       where  s0 . emp_name  =  s2 . emp_name

                                            or  s0 . prev_date  is  null)

解惑七：

1、查询语句

（1）子查询中，是使用Rank函数将表Salaries按emp_name分组且按sal_date降序排列

（2）查询语句中只有leftouterjoin表插入第二名的日期且选择第一日期即可。

with  SalaryRanks ( emp_name , sal_date , sal_amt , pos )

as

(

select  emp_name , sal_date , sal_amt ,

          RANK ()  over ( PARTITION  BY  emp_name  order  by  sal_date  desc )

   from  Salaries

)                              

select  C . emp_name ,

          C . sal_date  as  curr_date , C . sal_amt  as  curr_amt ,

          P . sal_date  as  prev_date , P . sal_amt  as  prev_amt

   from  SalaryRanks  as  c

   left  outer  join  SalaryRanks  as  P

     on  P . emp_name  =  c . emp_name

    and  P . pos  =  2

  where  c . pos  =  1

解惑八：

1、查询语句

（1）子查询将表Salaries按emp_name分组按sal_date降序排列并赋值

（2）where子句筛序序号小于3的数据

（3）select子句根据序号1或者2来确定最近的一次和最近一次的前一次

select  s1 . emp_name ,

          MAX ( case  when  rn  =  1  then  sal_date  else  null  end )  as  curr_date ,

          MAX ( case  when  rn  =  1  then  sal_amt  else  null  end )  as  curr_amt ,

          MAX ( case  when  rn  =  2  then  sal_date  else  null  end )  as  prev_date ,

          MAX ( case  when  rn  =  2  then  sal_amt  else  null  end )  as  prev_amt

   from  ( select  emp_name , sal_date , sal_amt ,

                      RANK ()  over  ( PARTITION  BY  emp_name  order  by  sal_date  desc )

               from  Salaries )  as  s1 ( emp_name , sal_date , sal_amt , rn )

  where  rn  <  3

  group  by  s1 . emp_name

解惑九：

该方法与解惑七中一样

with  cte ( emp_name , sal_date , sal_amt , rn )

as

(

select  emp_name , sal_date , sal_amt ,

        ROW_NUMBER ()  over  ( PARTITION  by  emp_name  order  by  sal_date  desc )  as  rn

   from  Salaries

)

select  o . emp_name ,

          o . sal_date  as  curr_date , o . sal_amt  as  curr_amt ,

          i . sal_date  as  prev_date , i . sal_amt  as  prev_amt

   from  cte  as  o

   left  outer  join  cte  as  i

     on  o . emp_name  =  i . emp_name  and  i . rn  =  2

  where  o . rn  =  1

解惑十：

·1、该语句在MSSQL2010、MSSQL2012的版本才能运行

select  emp_name , curr_date , curr_amt , prev_date , prev_amt

   from  ( select  emp_name ,

                      sal_date  as  curr_date ,

                      sal_amt  as  curr_amt ,

                      MIN ( sal_amt )  over  ( partition  by  emp_name

                                                   order  by  sal_date  desc

                                                   rows  between  1  following  and  following )

                      as  prev_date ,

                      min ( sal_amt )  over  ( partition  by  emp_name

                                                   order  by  sal_date  desc

                                                   rows  between  1  following  and  following )

                      as  prev_amt ,

                      row_number ()  over  ( partition  by  emp_name

                                                   order  by  sal_date  desc )

                      as  rn

               from  Salaries )  as  dt  

  where  rn  =  1