MySQL 包含带有 order by 的特定行

Question

给定 2 个表，我想从 [Purchase] 表中生成前 3 个最高金额。附加条件是 [Crocs] 必须包含在前 3 条记录中。

我有以下SQL，但它无法生成我想要的结果(结果A)，请指导我如何提取结果B中的结果。谢谢。

表(购买):

Purchase_ID | StoreID | Amount
------------|---------|--------
  1         |  21     |   22
  2         |  23     |   13
  3         |  25     |   6
  4         |  26     |   23
  5         |  28     |   18

表(商店):

Store_ID | StoreName     
---------|----------
  21     |  Adidas
  22     |  Nike
  23     |  Puma
  24     |  New Balance
  25     |  Crocs
  26     |  Converse

SQL:

SELECT IF(SUM(amount) IS NULL, 0, SUM(amount)) as totalAmount
FROM (
    SELECT a.amount
    FROM purchase a
    INNER JOIN store b
    ON a.store_id = b.storeid
    GROUP BY a.amount
    HAVING b.StoreName = 'Crocs'
    ORDER BY a.amount DESC
    LIMIT 3
) t

结果 A:$6

解释 A:Crocs 的数量为 6 美元

结果 B:51 美元

解释 B:前 3 名的总金额 = $22 (Adidas) + 23 (Puma) + $6 (Crocs)

Answer 1

scaisEdge 的答案几乎是正确的，但第一个查询也可能返回带有 crocs 的行，并且排序是错误的(order by max(a.amount) limit 2 意味着将显示最低的 2 个结果)。此外，您可以将查询包装在另一个选择查询中以对结果进行排序

  SELECT * FROM (
    SELECT b.storename, max(a.amount) as maxAmount
      FROM purchase a
      INNER JOIN store b ON a.store_id = b.storeid
      WHERE b.storename != 'crocks'
      GROUP BY a.storename
      ORDER BY max(a.amount) DESC 
      LIMIT 2
    UNION 
      SELECT b.storename, a.amount as maxAmount
      FROM purchase a
      INNER JOIN store b
      ON a.store_id = b.storeid
      WHERE b.storename='crocks'
      ORDER BY a.amount DESC
      LIMIT 1
  ) ORDER BY maxAmount DESC