mysql - 如何在 mySQL 中正确包装 sum(quantity) = max(sum(quantities)) ？

Question

我想要取每个唯一属性的总和，然后取这些值的最大值。关于如何使用 mySQL 执行此操作有什么建议吗？我不断收到“无效使用群组功能”的消息。

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| matchName   | varchar(26) | YES  | MUL | NULL    |       |
| winner      | varchar(26) | YES  |     | NULL    |       |
| loser       | varchar(26) | YES  |     | NULL    |       |
| winningGame | varchar(26) | YES  |     | NULL    |       |
| dateofWin   | date        | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

和

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| matchName  | varchar(26) | NO   | PRI |         |       |
| date       | date        | YES  |     | NULL    |       |
| grandPrize | int(11)     | YES  |     | NULL    |       |
| location   | varchar(26) | YES  |     | NULL    |       |
| state      | varchar(26) | YES  |     | NULL    |       |
| attendance | int(11)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

到目前为止，我最接近的是使用:

select Results.winner, Tourney.grandPrize 
from Tourney 
inner join Results 
on Results.matchName=Tourney.matchName;

Answer 1

你的问题很不清楚，

我已经投票给@A.D.评论，您应该提供更多详细信息，表结构，数据样本，预期结果，当前带来错误的查询等。

但是您的问题的快速答案是:

SELECT t.*, MAX(t.sum_qty) as max_qty
FROM (
  SELECT *, SUM(qty) as sum_qty
  FROM test
  GROUP BY order_id // you can set any group field you need here
) AS t