1. 查询重复出现次数最多的记录,按出现次数倒序排列
SELECT pid,COUNT(*) AS count FROM `tb_user` GROUP BY pid ORDER BY count DESC LIMIT 20;
结果:
pid | count | |
---|---|---|
781 | 9 | |
688 | 8 | |
520 | 7 | |
609 | 7 | |
810 | 6 | |
665 | 5 | |
666 | 3 |
2. 查询昨天的数据
SELECT * FROM `tb_order` WHERE paytime < UNIX_TIMESTAMP(CAST(SYSDATE() AS DATE)) AND paytime >= UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY);
公式:SELECT * FROM 表名 WHERE 时间戳字段名 < 今天0点时间戳 AND >= 昨天0点时间戳
PHP:
$today = strtotime("Today",time());
$yesterday = $today-86400;
echo $today; // 1603296000
echo date('Y-m-d H:i:s',$today); // 2020-10-22 00:00:00
echo date('Y-m-d H:i:s',$yesterday); // 2020-10-21 00:00:00
// TP5查询条件
$where =array(
'createTime'=>array('between',array(date('Y-m-d H:i:s',$yesterday),date('Y-m-d H:i:s',$today)))
);
3. 查询2小时间内的数据
SELECT * FROM 表名 WHERE 字段名 > DATE_SUB(NOW(), INTERVAL 120 MINUTE);
4.