MySQL - 过滤日期最大的记录

Question

我有这个 mysql 选择查询:

SELECT s.* 
FROM campaign_statistic AS s INNER JOIN campaign AS c ON s.campaign_id = c.campaign_id 
WHERE c.campaign_id = 51 
AND date(s.created_date) BETWEEN date(c.start_date) AND date(c.end_date)

得到这个结果:

id  campaign_id     instagram_id    media_id                        has_tag     comments_count  likes_count     created_date            status
**  ***********     ************    ****************************    ********    *************   ***********     *******************     ******
1   51              1230544324      957801995790641919_1230544324   together    1               16              2015-11-01 13:10:29     1
2   51              1230544324      957799073015065299_1230544324   together    2               11              2015-11-01 13:10:29     1
3   51              1230544324      957790310736521811_1230544324   together    5               20              2015-11-01 13:10:29     1
4   51              1230544324      957801995790641919_1230544324   together    1               16              2015-11-02 13:10:29     1
5   51              1230544324      957799073015065299_1230544324   together    2               11              2015-11-02 13:10:29     1
6   51              1230544324      957790310736521811_1230544324   together    5               20              2015-11-02 13:10:29     1
7   51              1230544324      957801995790641919_1230544324   together    1               16              2015-11-03 13:10:29     1
8   51              1230544324      957799073015065299_1230544324   together    2               11              2015-11-03 13:10:29     1
9   51              1230544324      957790310736521811_1230544324   together    5               20              2015-11-03 13:10:29     1

但我试图仅从上述结果中获取 created_date 最大的那些记录，而最大的 created_date 是 2015-11-03。

我需要这个结果:

id  campaign_id     instagram_id    media_id                        has_tag     comments_count  likes_count     created_date            status
**  ***********     ************    ****************************    ********    *************   ***********     *******************     ******
7   51              1230544324      957801995790641919_1230544324   together    1               16              2015-11-03 13:10:29     1
8   51              1230544324      957799073015065299_1230544324   together    2               11              2015-11-03 13:10:29     1
9   51              1230544324      957790310736521811_1230544324   together    5               20              2015-11-03 13:10:29     1

有什么想法或建议吗？

谢谢。

Answer 1

你能试试运行这个吗:

SELECT s.* 
FROM campaign_statistic AS s INNER JOIN campaign AS c ON s.campaign_id = c.campaign_id 
WHERE c.campaign_id = 51 
AND date(s.created_date) BETWEEN date(c.start_date) AND date(c.end_date)
    AND date(s.created_date) = (
    SELECT date(max(s.created_date)) 
FROM campaign_statistic AS s INNER JOIN campaign AS c ON s.campaign_id = c.campaign_id 
WHERE c.campaign_id = 51 
AND date(s.created_date) BETWEEN date(c.start_date) AND date(c.end_date))