获取每个post\u id的最后结果

hmtdttj4 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(391)

我用的是 mysqlnd 5.0.12-dev 数据库作为我的后端，我想回到，在最后一行 wp_productprofitability 对于每个post\u id。
我有以下设置： 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 |
+----+--------------+---------+-------------------+---------------------+

对于以下查询，我只返回以下结果：

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 |
+----+--------------+---------+-------------------+---------------------+

有什么建议可以让每个帖子都知道最后发生的事情吗？
感谢您的回复！

sql mysql Database

来源：https://stackoverflow.com/questions/51124278/get-last-result-back-for-each-post-id

2条答案

按热度按时间

dpiehjr41#

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

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

赞(0）回复(0）举报 2021-06-20

ftf50wuq2#

我会将您的查询短语化为一个子查询，该子查询查找表中最近的记录 wp_productprofitability 对于每个 post_id :

SELECT
    t1.ID,
    t1.title,
    t2.post_id,
    t2.daily_grossProfit,
    t2.created_at
FROM wp_post t1
INNER JOIN wp_productprofitability t2
    ON t1.ID = t2.post_id
INNER JOIN
(
    SELECT post_id, MAX(created_at) AS max_created_at
    FROM wp_productprofitability
    GROUP BY post_id
) t3
    ON t2.post_id = t3.post_id AND t2.created_at = t3.max_created_at;

请参阅gordon的答案，以更正您的确切问题。基本上，您需要向子查询中添加逻辑，以限制子查询中的每个记录 wp_productprofitability 到每个的最新版本 post_id . 但我不会在这里使用相关子查询，连接可能会比它表现更好。

展开查看全部

赞(0）回复(0）举报 2021-06-20

我来回答

获取每个post\u id的最后结果

2条答案

相关问题

热门标签

最新问答