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

冠军"/>

【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