mysql - 针对不同的条件，从同一张表中选择 max(date) 两次

Question

这是我的表格的一小段，目前包含约 10,000,000 行

+---------+---------------------+-----------+----------------+
| card_id | date                | avg_price | foil_avg_price |
+---------+---------------------+-----------+----------------+
|   10000 | 2014-06-28 09:05:56 |      5.02 |          10.22 |
|   20000 | 2014-06-28 09:05:54 |     14.58 |          25.10 |
|   10000 | 2014-06-29 09:05:56 |      0.00 |          19.62 |
|   20000 | 2014-06-29 09:05:54 |     14.58 |          0.00  |
|   10000 | 2014-07-01 09:05:56 |      0.00 |          19.62 |
|   20000 | 2014-07-01 09:05:54 |      0.00 |          25.10 |
+---------+---------------------+-----------+----------------+

这是卡片的价格历史记录，包括每天左右的 avg_price 和 foil_avg_price 是多少。

我想为一组卡片 ID 选择 foil_avg_price > 0 时的最新日期、该价格是多少、avg_price > 0 的最近日期以及该价格是多少。我的上述结果数据集看起来像这样:

+---------+---------------------+-----------+---------------------+----------------+
| card_id | avg_date            | avg_price | foil_date           | foil_avg_price |
+---------+---------------------+-----------+---------------------+----------------+
|   10000 | 2014-06-28 09:05:56 |      5.02 | 2014-07-01 09:05:54 |          19.62 |
|   20000 | 2014-06-29 09:05:54 |     14.58 | 2014-07-01 09:05:54 |          25.10 |
+---------+---------------------+-----------+---------------------+----------------+

我确定这涉及同一张表上的 INNER JOIN，但我无法完全理解它。任何帮助将不胜感激。

Answer 1

三个步骤:

• 查找最后定价日期
• 查找最后的贴膜价格日期
• 确定这些日期的价格

所以，

SELECT dates.*, price.avg_price, foilprice.foil_avg_price 
FROM (
    SELECT 
      card_id,
      MAX(IF(avg_price>0, `date`, '0001-01-01')) AS avg_date,
      MAX(IF(foil_avg_price>0, `date`, '0001-01-01')) AS foil_avg_date
    FROM card_price
    GROUP BY card_id
  ) AS dates
  INNER JOIN card_price AS price
    ON dates.card_id=price.`date`
  INNER JOIN card_price AS foilprice
    ON dates.card_id=foilprice.`date`