17,086
社区成员
发帖
与我相关
我的任务
分享
--第三个
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;
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
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;