group by笔试习题

group by笔试练习
SalesOrder 合同表

统计：

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

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

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

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

 

orderID(主键) 地区region 销售人员sale    合同总额total 
1            A            张三           10000 
2            A            李四           12000 
3            B            王五           30000 
4            C            小刘           20000 
5            C            小马           23000 

第三个，第四个不知道怎么写？
------解决方案--------------------

--第三个
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;

------解决方案--------------------
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;
------解决方案--------------------

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 (