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

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

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