Sql 书写技巧1

mysql的表在图中。

数据库中有一个表名为salesOrder的表
orderID(PK,合同ID)        region(地区)        sales(销售人员)     total(合同金额)
1                                         A                            张三                          1000     
2                                         A                            李四                           5000
3                                         C                            赵五                          3000
4                                         A                             张三                          4000
5                                         C                            赵五                          2000
6                                         A                             张三                          3000
7                                         B                            王六                           5000
8                                         B                            钱七                           2500
9                                         B                            王六                           6000
 
一：统计出每个地区的合同金额合计并按此倒序排列显示

 
 

  
  

   
   

    
    [sql] 
    
    view plain
    
    copy
    
    

   
   
  
  
  
  
SELECT region ,sum(total) as rt FROM salesorder group by region order by rt desc ;
注释：sum(total) as rt 的结果可以直接使用到 order by 中。
 
 
二：统计出每个地区的销售人员数量

 
 

  
  

   
   

    
    [sql] 
    
    view plain
    
    copy
    
    

   
   
  
  
  
  
select region,count(distinct(sales)) from salesorder group by region;  
注意：去除重复的销售人员


 
 
三：统计出每个地区合同金额最少的销售人员

 
 

  
  

   
   

    
    [sql] 
    
    view plain
    
    copy
    
    

   
   
  
  
  
  
SELECT t3.region, t3.sales, t3.st
FROM (
    
    

     
            SELECT 
     
     region
     
     , 
     
     sales
     
     , 
     
     sum
     
     ( 
     
     total 
     
     )  
     
     AS 
     
     st

     
            FROM  
     
     `salesorder` 

     
            GROUP  
     
     BY 
     
     region
     
     , 
     
     sales
    
    
)t3, (
    
    

     
            SELECT 
     
     region
     
     , 
     
     min
     
     ( 
     
     t
     
     .
     
     st 
     
     )  
     
     AS 
     
     st

     
            FROM 
     
     
     
     (
     
     

      
       SELECT 
      
      region
      
      , 
      
      sales
      
      , 
      
      sum
      
      ( 
      
      total 
      
      )  
      
      AS 
      
      st

      
       FROM  
      
      `salesorder` 

      
       GROUP  
      
      BY 
      
      region
      
      , 
      
      sales
     
     
     
                )
     
     t

     
                 GROUP  
     
     BY 
     
     region
    
    
)t2
WHERE t3.region = t2.region
AND t3.st = t2.st
注意：先弄出 每个地区合同金额最少 的数据再与自身进行连接。
自身连接自身很重要
 
 
四：统计出所有超过本地区合同金额平均值的合同及金额
[sql] view plaincopy
 
 

  
  

   
   

  
  
 
 
 
 
select distinct q.* from (select avg(total) as avgtotal,region  from salesorder group by region) p,salesorder  
q where q.total>p.avgtotal;  

问题如下：
1、统计出每个地区的销售人员数量
2、统计出每个地区合同金额最少的销售人员
3、统计出所有超过本地区合同金额平均值的合同及金额
这号称是一个面试题，是不是用到了存储过程？
求高手帮忙解答

可以不用存储过程
1, select region ,count(distinct(sales)) from SalesOrder group by region

2, select a.region,a.sales from SalesOrder a,（select region,min(total) total from SalesOrder group by region）b where a.total=b.total and a.region=b.region

3, select a.orderID,a.total from SalesOrder a,(select region,avg(total) total from SalesOrder group by region) b where a.region=b.region and a.total>b.total

1.每个地区的销售总额并降序排列

2.每个地区的销售人员数量

3.每个地区销售金额最少的销售人员

4.各个地区所有超过每个地区合同额的平均值的合同，销售人员

select sum(total),region from a05 group by region order by 1 desc;

select count(*),region from a05 group by region;

select sale,region from (
select sale,region,row_number() over(partition by region order by total) rm from a05)
where rm=1;

select a05.region,total from a05,(select avg(total) avgtotal,region from a05 group by region) t
where a05.total > t.avgtotal
and a05.region = t.region;