sql - 如何仅显示内部连接表列的最大值？

Question

我在两个表上使用了 INNER JOIN :

Transactions 
 - transaction_id (PK)
 -ticket_id (FK) reference to ticketsforsale

Ticketsforsale : 
 - ticket_id (PK)
 - type
 - price 

(每个表中还有更多列，但对本题没有任何意义)

我试过的查询如下:

SELECT ticketsforsale.type , SUM(ticketsforsale.price) AS TotalProfit
   FROM ticketsforsale INNER JOIN transactions 
   ON ticketsforsale.ticket_id = transactions.ticket_id 
GROUP BY ticketsforsale.type 

The result is : 
  Sports | 300
  Cruise | 600
  Theater| 100

我尝试在查询中使用这一行

WHERE TotalProfit = SELECT(MAX(TotalProfit)

但我想不出这条线的正确位置。

我想要查询做的是只显示包含 "TotalProfit"最大值的行。我只是缺少此查询的正确 MAX 函数用法，谢谢!

Answer 1

使用 ORDER BY 并将结果集限制为一行:

SELECT tfs.type , SUM(tfs.price) AS TotalProfit
FROM ticketsforsale tfs INNER JOIN
     transactions t 
     ON tfs.ticket_id = t.ticket_id 
GROUP BY tfs.type 
ORDER BY TotalProfit DESC
FETCH FIRST 1 ROW ONLY;

请注意，我还引入了表别名，因此查询更易于编写和阅读。

根据此查询，您似乎不需要JOIN:

SELECT tfs.type , SUM(tfs.price) AS TotalProfit
FROM ticketsforsale tfs 
GROUP BY tfs.type 
ORDER BY TotalProfit DESC
FETCH FIRST 1 ROW ONLY;