mysql - 获取每个 post_id 的最后结果

Question

我使用 mysqlnd 5.0.12-dev 数据库作为后端，我想返回每个 post_id 的 wp_productprofitability 中的最后一行。

我有以下设置:

wp_post:

+----+--------------+
| ID | title        |
+----+--------------+
| 10 | My Product 1 |
+----+--------------+
| 11 | My Product 2 |
+----+--------------+
| 6  | My Product 3 |
+----+--------------+

wp_productprofitability

+---------+-------------------+---------------------+
| post_id | daily_grossProfit | created_at          |
+---------+-------------------+---------------------+
| 10      | 3.5               | 2018-06-18 07:36:00 |
+---------+-------------------+---------------------+
| 11      | 4                 | 2018-06-17 07:35:00 |
+---------+-------------------+---------------------+
| 10      | 5                 | 2018-06-16 07:34:00 |
+---------+-------------------+---------------------+
| 11      | 7                 | 2018-06-17 07:36:00 |
+---------+-------------------+---------------------+
| 10      | 5                 | 2018-06-15 07:36:00 |
+---------+-------------------+---------------------+
| 6       | 3.6               | 2018-06-12 07:34:00 |
+---------+-------------------+---------------------+
| 10      | 2                 | 2018-06-14 07:32:00 |
+---------+-------------------+---------------------+
| 6       | 6                 | 2018-06-13 07:31:00 |
+---------+-------------------+---------------------+

预期结果:

+----+--------------+---------+-------------------+---------------------+
| ID | title        | post_id | daily_grossProfit | created_at          |
+----+--------------+---------+-------------------+---------------------+
| 10 | My Product 1 | 10      | 3.6               | 2018-06-18 07:36:00 |
+----+--------------+---------+-------------------+---------------------+
| 11 | My Product 2 | 11      | 4                 | 2018-06-17 07:36:00 |
+----+--------------+---------+-------------------+---------------------+
| 6  | My Product 3 |  6      | 5                 | 2018-06-16 07:34:00 |
+----+--------------+---------+-------------------+---------------------+

Atm 对于以下查询，我只得到以下结果:

SELECT
    *
FROM
    wp_posts p
INNER JOIN wp_productprofitability m ON
    m.post_id = p.ID
WHERE
    m.created_at =(
    SELECT
        MAX(u.created_at)
    FROM
        wp_productprofitability u
)
ORDER BY
    m.daily_grossProfit
DESC

当前结果:

+----+--------------+---------+-------------------+---------------------+
| ID | title        | post_id | daily_grossProfit | created_at          |
+----+--------------+---------+-------------------+---------------------+
| 10 | My Product 1 | 10      | 3.6               | 2018-06-18 07:36:00 |
+----+--------------+---------+-------------------+---------------------+
| 11 | My Product 2 | 11      | 4                 | 2018-06-17 07:36:00 |
+----+--------------+---------+-------------------+---------------------+   

有什么建议如何获取每个 post_id 的最后一次出现吗？

感谢您的回复!

Answer 1

你很接近。您只需要一个关联子句:

SELECT *
FROM wp_posts p INNER JOIN
     wp_productprofitability pp
     ON pp.post_id = p.ID
WHERE m.created_at = (SELECT MAX(pp2.created_at)
                      FROM wp_productprofitability pp2
                      WHERE pp2.post_id = pp.post_id
                     )
ORDER BY pp.daily_grossProfit DESC