Question

I know there are already some solutions to this question but they don t seem to be working for me.

我想从每个用户每天在我的数据库中选择最后记录。我有一个数据库,按国家储存一个用户点,日期到第二个。

试图运行的选定问题Im是:

SELECT MAX(date), id, country FROM dk_location_records WHERE userid =  10 
AND (date >  2012-04-06 00:00:00  AND date <  2012-05-08 23:59:59 )
AND active =  1  GROUP BY DATE(date) ORDER BY date ASC

然而,实际情况是,我从一天起的最后一天,而不是正确的对应的<编码>id和country。该编码实际上与最后的<代码>一起获得第1条<代码>id和country。

我做了什么错误?

非常感谢帮助!

感谢

页: 1

Answer 1

询问每个用户每天显示最后记录的情况,

SELECT t1.* FROM dk_location_records t1
  JOIN (SELECT DATE(date) date_date, userid, MAX(date) max_date
        FROM dk_location_records
        GROUP BY date_date, userid
        ) t2
    ON t1.date = t2.max_date AND t1.userid = t2.userid;

......如果需要的话,满足你的“健康”条件。

Answer 2

我认为,你不应按<代码>DATE(date)分类。我认为,你应当按<代码>删除,国家分类。与此类似:

SELECT 
  MAX(date), userid, country 
FROM 
  dk_location_records 
WHERE 
  userid =  10 
  AND (date >  2012-04-06 00:00:00  AND date <  2012-05-08 23:59:59 )
  AND active =  1  
GROUP BY userid,country  
ORDER BY date ASC

Answer 3

You should get the correct results by changing your ORDER BY to DESC instead of ASC

友情链接