Question

php mysql 如何筛选出每天的最后一条数据呢?

php 程序里有这样一句：

$logs_result = $database->query("SELECT * FROM `weibo_logs` WHERE `date` BETWEEN '{$date_range_start}' AND DATE_ADD('{$date_range_end}', INTERVAL 1 DAY) AND `weibo_user_id` = '{$source_account->id}' ORDER BY `date` ASC");

我想在不改变原功能的前提下，加一个筛选，选出每天的最后一条数据，以上的代码需要如何修改呢？请指教，感谢！

Answer 1

查询今天最后一条数据
SELECT * FROM `weibo_logs` where DATEDIFF( NOW(),date)=0 order by `date` desc limit 1
查询符合条件的weibo_user_id每天的一条数据
SELECT weibo_user_id, DATE_FORMAT(date,'%Y-%m-%d') FROM `text` where 'weibo_user_id'='{$source_account->id}' GROUP BY DATE_FORMAT(date,'%Y-%m-%d')
这样会有排序问题，那么就先排序
SELECT DATE_FORMAT(date,'%Y-%m-%d') as d
FROM (SELECT * FROM `text` ORDER BY date DESC) as s
where 'weibo_user_id'='{$source_account->id}'
GROUP BY DATE_FORMAT(date,'%Y-%m-%d')

Answer 2

把SQL语句改一下，通过SQL语句查出同一天内最后一条记录：
select * from (select *,FROM_UNIXTIME(date, '%Y-%m-%d') as etime from weibo_logs order by date desc) as b group by b.etime;