1、查询表7天的记录(t_company 表名,create_time 时间字段名,CURDATE() 当前系统时间)
SELECT * FROM t_company where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
2、查询表30天的记录(t_company 表名,create_time 时间字段名,CURDATE() 当前系统时间)
SELECT * FROM t_company where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)
3、查询表数据,并且根据时间字段的年月日分组,通过GROUP BY DATE_FORMAT(create_time,"%Y%m%d")
SELECT COUNT(id),create_time FROM t_company where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time) GROUP BY DATE_FORMAT(create_time,"%Y%m%d")
4、按天统计数据,如果当天没有记录,则统计为0
参考链接
参考链接
t_company表名,t_operation_log表名,create_time 时间字段名
SELECT t1.day,COALESCE(t2.num,0) as num FROM
(SELECT
@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY FROM (SELECT @cdate := DATE_ADD(CURDATE(), INTERVAL + 1 DAY) FROM
t_company) t0 LIMIT 30) t1
LEFT JOIN (
SELECT count(id) num,DATE(t_operation_log.create_time) DAY
FROM t_operation_log GROUP BY DATE_FORMAT(create_time,"%Y%m%d"
) ORDER BY create_time DESC) t2 ON t2.day = t1.day GROUP BY t1.day
5、Mysql实行模糊查询,一个输入值匹配多个字段
参考链接
select * from 表名 where concat(字段1,字段2,) like '%关键字%'
6、查询两张表,不一样的数据
select * from B where (select count(1) from A where A.id = B.id) = 0