按收入对交易进行排序，这是MySQL中订单表行的总和(Sorting deals by their revenue, which is sum of orders table rows in MySQL)

 好吧，我一直质疑这一点，但直到今天才真正需要它。 
 
 通常情况下，我会抓住所有交易，分别查看所有交易，并从与此交易相关的所有订单中找到SUM（金额）。 （PHP） 
 
 然后我会做一个简单的uasort（）工作正常。 
 
 但现在我需要使用sql查询来完成所有操作。 
 
 这是我尝试过的： 
 
SELECT deals.ID, SUM(orders.amount) AS revenue FROM deals
JOIN orders ON (orders.deal_id = deals.id)
ORDER BY revenue DESC
 
 现在这给了我一行，带有“随机”交易ID，收入数字更大。 
 
 我怀疑收入中的这个数字是所有金额列的总和，而不是这个特定的交易ID。 
 
 我想要的是每个交易ID的一行，并且在列收入中有正确的数字，所有行都排序DESC。 
 
 这怎么办？ 要对交易进行排序，请从其收入（自定义列）中进行排序 - 这是相应交易ID的行中所有金额列的总和。 

Okay I always have questioned this, but never really needed it until today.
 
Normally, I would grab all deals, go through them each and find the SUM(amount) from all the orders related to this deal. (PHP)
 
Then I would do a simple uasort() which works fine.
 
But now I need to do it all with a sql query.
 
This is what I have tried:
 
SELECT deals.ID, SUM(orders.amount) AS revenue FROM deals
JOIN orders ON (orders.deal_id = deals.id)
ORDER BY revenue DESC
 
Now this gives me one row, with a 'random' deal ID, and a bigger number in revenue.
 
I suspect this number in revenue is the SUM of ALL the amount columns, and not for this particular deal ID.
 
What I would like is a row for each deal ID, and with the right number in the column revenue, all the rows sorted DESC.
 
How would this be done? To sort the deals, out from their revenue (custom column) - which is the sum of all the amount columns from the rows for the respective deal id.

原文：https://stackoverflow.com/questions/13435139