group by笔试练习

Question

SalesOrder 合同表统计：1.每个地区的销售总额并降序排列2.每个地区的销售人员数量3.每个地区销售金额最少的销售人员4.各个地区所有超过每个地区合同额的平均值的合同，销售人员 orderID(主键) 地区region 销售人员sale    合同总额total 
1            A            张三           10000 
2            A            李四           12000 
3            B            王五           30000 
4            C            小刘           20000 
5            C            小马           23000 第三个，第四个不知道怎么写？

Answer 1

--第三个
select region,sale from SalesOrder s1
where total=(select min(total)
from SalesOrder s2
where s1.region=s2.region);
--第四个(所有超过),因此B区不包括，若改为s1.total>=s2.avgtotal则可包括B
select s1.region,s1.orderID from SalesOrder s1,(
select region,avg(total) avgtotal from SalesOrder group by region) s2
where s1.region=s2.region and s1.total>s2.avgtotal;

Answer 2

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;

Answer 3

WITH SalesOrder AS (
SELECT 1 orderID,'A' region,'張三' sale,10000 total FROM dual
UNION ALL 
SELECT 2,'A','李四',12000 FROM dual
UNION ALL 
SELECT 3,'B','王五',30000 FROM dual
UNION ALL 
SELECT 4,'C','小劉',20000 FROM dual
UNION ALL 
SELECT 5,'C','小馬',23000 FROM dual
)
--第3题
--SELECT region,sale,total FROM  SalesOrder t WHERE total=(SELECT Min(total) FROM SalesOrder WHERE region=t.region);
SELECT orderID,region,sale,total FROM (
  SELECT orderID,region,sale,total,Row_Number() over (PARTITION BY region ORDER BY total)rn FROM SalesOrder
)WHERE rn=1;
--第4题
SELECT orderID,region,sale,total,avg FROM (
  SELECT orderID,region,sale,total,Avg(total) over (PARTITION BY region)Avg FROM SalesOrder
)WHERE total>=avg;

Answer 4

WITH T AS
(
SELECT 1 orderID, 'A' region,'张三' sale,10000 total FROM DUAL  
UNION ALL
SELECT 2, 'A', '李四', 12000   FROM DUAL
UNION ALL
SELECT 3, 'B', '王五', 30000  FROM DUAL
UNION ALL
SELECT 4, 'C', '小刘', 20000  FROM DUAL
UNION ALL
SELECT 5, 'C', '小马', 23000 FROM DUAL
)
SELECT A.ORDERID,A.REGION,A.SALE 
    FROM T A,
      ( SELECT REGION,MIN(TOTAL) MTOTAL 
               FROM T
          GROUP BY REGION) B
    WHERE A.REGION=B.REGION AND A.TOTAL=B.MTOTAL
ORDERID                REGION SALE   
---------------------- ------ ------ 
1                      A      张三   
3                      B      王五   
4                      C      小刘   3 rows selected
SELECT A.ORDERID,A.SALE,A.REGION
    FROM T A,
        (SELECT REGION,AVG(TOTAL) AVGS
               FROM T
          GROUP BY REGION) B
  WHERE A.REGION=B.REGION AND A.TOTAL>B.AVGS
ORDERID                SALE   REGION 
---------------------- ------ ------ 
2                      李四   A      
5                      小马   C      2 rows selected

Answer 5

mysql的sql：
1. select region,sum(total)  from SalesOrder group by region order by sum(total) DESC;
2. select region,count(sale) from SalesOrder group by region; 
3. select region,sale,total from SalesOrder where (region,total) in (select region,min(total) from SalesOrder group by region);
4.select s1.orderID,s1.region,s1.sale,s1.total 
from salesorder s1, (select region, avg(total) avgtotal from salesorder group by region) s2 where s1.region=s2.region and s1.total>=s2.avgtotal;
本人运行了，没有问题