需要从2个表

Question

检索最大值和最小值我有以下2个表

表名称：Port

City  Code 
------------------ 
City A  001 
City B  002 
City C  003 
City D  004 
City E  005 
City F  006 
City G  007 

表名：出货量

Code  Shipments 
------------------ 
001   5 
001   4 
002   2 
003   4 
003   3 
003   4 
004   1 
005   1 
006   1 
007   2 

我想要得到的发生最大运输量的城市清单。

答：

City  Total Shipments 
------------------------ 
City C  11 
City D  1 
City E  1 
City F  1 

有人可以帮助我的SQL查询这一点。

Answer 1

select c.city as City, sum(s.shipments) as Shipments 
from 
    port c 
inner join 
    shipments s on s.code = c.code  
group by c.code 
order by sum(s.shipments) asc; 

看看这个，让我知道。

Answer 2

您可以使用Common Table Expression（CTE）首先汇总每个城市的出货量。如下min和max记录然后可以返回：

WITH cte AS ( SELECT P.city AS City, 
        SUM(S.shipments) AS TotalShipments 
       FROM Port P 
       INNER JOIN Shipments S ON S.code = P.code  
       GROUP BY P.city) 
SELECT * 
FROM cte c 
WHERE c.TotalShipments IN (SELECT MAX(c2.TotalShipments) RequiredTotalShipments 
          FROM cte c2 
          UNION 
          SELECT MIN(c3.TotalShipments) 
          FROM cte c3)