【SQL解惑】谜题19：销售冠军

一、创建和查询语句

create table SalesData

( district_nbr integer not null,

sales_person char ( 10 )not null,

sales_id integer not null,

sales_amt decimal ( 5 , 2 ) not null)

insert into SalesData ( district_nbr , sales_person , sales_id , sales_amt )

values ( 1 , 'Curly' , 5 , 3.00 ),

( 1 , 'Harpo' , 11 , 4.00 ),

( 1 , 'Larry' , 1 , 50.00 ),

( 1 , 'Larry' , 2 , 50.00 ),

( 1 , 'Larry' , 3 , 50.00 ),

( 1 , 'Moe' , 4 , 5.00 ),

( 2 , 'Dick' , 8 , 5.00 ),

( 2 , 'Fred' , 7 , 5.00 ),

( 2 , 'Harry' , 6 , 5.00 ),

( 2 , 'Tom' , 7 , 5.00 ),

( 3 , 'Irving' , 10 , 5.00 ),

( 3 , 'Melvin' , 9 , 7.00 ),

( 4 , 'Jenny' , 15 , 20.00 ),

( 4 , 'Jessie' , 16 , 10.00 ),

( 4 , 'Mary' , 12 , 50.00 ),

( 4 , 'Oprah' , 14 , 30.00 ),

( 4 , 'Sally' , 13 , 40.00 )

二、查询语句

1、解惑一

方法一：

select *

  from SalesData as s0

  where s0 . sales_amt <= ( select MAX ( s1 . sales_amt )

                                 from SalesData as s1

                                where s0 . district_nbr = s1 . district_nbr

                                  and s0 . sales_amt <= s1 . sales_amt

                               having COUNT ( distinct s1 . sales_person ) <= 3 )

这个的作用：

select MAX ( s1 . sales_amt )

                                 from SalesData as s1

                                where s0 . district_nbr = s1 . district_nbr

                                  and s0 . sales_amt <= s1 . sales_amt

                               having COUNT ( distinct s1 . sales_person )

筛选出每个地区中独立销售额数大于3的且最大的销售额

方法二：

select *

  from SalesData as s0

  where sales_amt >= ( select min ( s1 . sales_amt )

                                 from SalesData as s1

                                where s0 . district_nbr = s1 . district_nbr

                                  and s0 . sales_amt <= s1 . sales_amt

                               having COUNT (*) <= 3 )

  order by s0 . district_nbr , s0 . sales_person , s0 . sales_id , s0 . sales_amt

2、解惑二（OLAP函数）

select s1 . distric_nbr , s1 . sales_person , s1 . rank_nbr

  from ( select district_nbr , sales_person ,

                     RANK ()

                     over ( PARTITION by district_nbr

                           order by sales_amt desc )

              from SalesData )

             as s1 ( distric_nbr , sales_person , rank_nbr )

  where s1 . rank_nbr <= 3