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