MySQL - 过滤日期最大的记录 [英] MySQL - Filter records which date is biggest

问题描述

我有这个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在一起1 16 2015-11-01 13:10:29 1 
 2 51 1230544324 957799073015065299_1230544324在一起2 11 2015-11-01 13:10:29 1 
 3 51 1230544324 957790310736521811_1 230544324在一起5 20 2015-11-01 13:10:29 1 
 4 51 1230544324 957801995790641919_1230544324在一起1 16 2015-11-02 13:10:29 1 
 5 51 1230544324 957799073015065299_1230544324在一起2 11 2015- 11-02 13:10:29 1 
 6 51 1230544324 957790310736521811_1230544324在一起5 20 2015-11-02 13:10:29 1 
 7 51 1230544324 957801995790641919_1230544324在一起1 16 2015-11-03 13:10 ：29 1 
 8 51 1230544324 957799073015065299_1230544324在一起2 11 2015-11-03 13:10:29 1 
 9 51 1230544324 957790310736521811_1230544324在一起5 20 2015-11-03 13:10:29 1 
  

但是我试图获得onl那些 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在一起1 16 2015-11-03 13:10:29 1 
 8 51 1230544324 957799073015065299_1230544324在一起2 11 2015-11-03 13:10:29 1 
 9 51 1230544324 957790310736521811_1230544324在一起5 20 2015-11-03 13:10:29 1 
  

任何想法或建议？

谢谢。

解决方案

你可以尝试运行这个：

  SELECT s。* 
 FROM c ampaign_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））
  

I have this mysql select query:

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 getting this result:

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

but I am trying to get only those records which created_date is biggest from above result and biggest created_date is 2015-11-03.

I need this result:

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

Any idea or suggestion?

Thanks.

解决方案

Could you try running this:

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

这篇关于MySQL - 过滤日期最大的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！