SalesOrder 合同表
统计：
1.每个地区的销售总额并降序排列
2.每个地区的销售人员数量
3.每个地区销售金额最少的销售人员
4.各个地区所有超过每个地区合同额的平均值的合同，销售人员
orderID(主键) 地区region 销售人员sale 合同总额total
1 A 张三 10000
2 A 李四 12000
3 B 王五 30000
4 C 小刘 20000
5 C 小马 23000
第三个，第四个不知道怎么写？
------解决方案--------------------
- SQL code
--第三个select region,sale from SalesOrder s1 where total=(select min(total) from SalesOrder s2 where s1.region=s2.region);--第四个(所有超过),因此B区不包括，若改为s1.total>=s2.avgtotal则可包括Bselect s1.region,s1.orderID from SalesOrder s1,(select region,avg(total) avgtotal from SalesOrder group by region) s2where 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;
------解决方案--------------------
- SQL code
WITH SalesOrder AS (SELECT 1 orderID,'A' region,'張三' sale,10000 total FROM dualUNION ALL SELECT 2,'A','李四',12000 FROM dualUNION ALL SELECT 3,'B','王五',30000 FROM dualUNION ALL SELECT 4,'C','小劉',20000 FROM dualUNION 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;
------解决方案--------------------
- SQL code
WITH T AS(SELECT 1 orderID, 'A' region,'张三' sale,10000 total FROM DUAL UNION ALLSELECT 2, 'A', '李四', 12000 FROM DUALUNION ALLSELECT 3, 'B', '王五', 30000 FROM DUALUNION ALLSELECT 4, 'C', '小刘', 20000 FROM DUALUNION ALLSELECT 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.MTOTALORDERID REGION SALE ---------------------- ------ ------ 1 A 张三 3 B 王五 4 C 小刘 3 rows selectedSELECT 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.AVGSORDERID SALE REGION ---------------------- ------ ------ 2 李四 A 5 小马 C 2 rows selected