学校表 lqy_educationadd 学校id_ 区域 area
教师表 lqy_teacher 外键schoolId
一个学校关联多个教师
查询区域下的 区域 教师数量
select
a.area,count(b.id_) from
lqy_educationadd a, lqy_teacher b
where
a.status = 0 and b.status = 0 and a.id_ = b.schoolId group by a.area
查询结果
--------------------------------------------
知识点:
- TO_DAYS函数 返回一个天数从年份0开始的天数
例如 SELECT TO_DAYS(NOW()); 从0年开始到现在的天数
- IFNULL(expression, alt_value) 函数 如果第一个参数的表达式 expression 为 NULL,则返回第二个参数的备用值
下面是 SELECT 语句:
SELECT NOW()
SELECT CURDATE()
SELECT CURTIME()
结果如下所示:
Mysql查询当天,本周,本月等所有数据记录
模拟数据 当天为2020-10-20 该表为sys_user
一、查询当天的记录:
#获取今天数据
select * from sys_user where TO_DAYS(create_time) = TO_DAYS(NOW());
注意:这里的create_time是数据库中的时间字段(datetime类型),会根据这个时间去和今天的时间对比获取数据.
二、查询昨天(固定几天前)的记录:
#获取昨天数据
select * from sys_user where TO_DAYS(NOW()) - TO_DAYS(create_time) = 1;
注意:这里的时间实际上和查询当天的记录一样,但是用了算术差的概念,同样的要获取前天的时间就将结果的1改成2即可,4天前的数据(某一天),则改为4即可
三、查询当前这周的数据:(当前是周二)
#获取这周数据
SELECT * FROM sys_user where YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1);
注意:这里的create_time是数据库中的时间字段,会根据这个时间去和今天的时间对比获取数据,除此之外,这个SQL语句获取的是美国的一周数据,中国和美国的周期不一样,美国的周日是中国的周一,所以如果要按照中国的周获取结果,需要推迟一天,
四、查询本月的数据:
#获取本月数据
SELECT * FROM sys_user where DATE_FORMAT(create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );
注意:这里的create_time是数据库中的时间字段,会根据这个时间去和今天的时间对比获取数据.
函数 PERIOD_DIFF(P1,P2) - 返回两个周期的月数
若要查询上月数据 PERIOD_DIFF(P1,P2) = 1 本月=0 下月 = -1 注意此处的事件格式'%Y%m' 中间没有分隔符'-'
#查询上月数据
select * from sys_user where PERIOD_DIFF(DATE_FORMAT(NOW() ,'%Y%m'), DATE_FORMAT(create_time,'%Y%m')) = 1;
或者
select * from sys_user where DATE_FORMAT(create_time ,'%Y-%m') = DATE_FORMAT(DATE_SUB(NOW(),interval 1 MONTH),'%Y-%m');
若查询距离当前现在6个月的数据(第六个月,不是前面6个月加起来) 则改为 DATE_SUB(NOW(),interval 6 MONTH) 即可
五、查询本年的数据:
#获取本年数据
SELECT * FROM sys_user where DATE_FORMAT(create_time, '%Y' ) = DATE_FORMAT( CURDATE( ) , '%Y' );
或者
SELECT * FROM sys_user where YEAR(create_time) = YEAR( CURDATE( ));
注意:这里的create_time是数据库中的时间字段,会根据这个时间去和今天的时间对比获取数据.
六、其余相关查询
1.查询本季度数据
select * from sys_user where QUARTER(create_time) = QUARTER(NOW());
2.查询上季度数据
DATE_SUB() 函数从日期减去指定的时间间隔。 DATE_SUB(date,INTERVAL expr type) date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔 type可以是如下值
select * from sys_user where QUARTER(create_time) = QUARTER(DATE_SUB(NOW(),interval 1 QUARTER));
3.查询上年数据
select * from sys_user where YEAR(create_time) = YEAR(DATE_SUB(NOW(),interval 1 YEAR));
4.查询近3天的数据(从今天开始往前推算,共计查询3天的数据) 若查询近30天的数据 则改为 DATE_SUB(CURDATE(), INTERVAL 30 DAY)
select * from sys_user where DATE_SUB(CURDATE(), INTERVAL 7 DAY) < DATE(create_time);
此处没有使用=,费泽就变成了查询4天的数据
七、查询当前日期之前一周内每天的数据
#查询当前日期之前一周内每天的数据条数
SELECT a.click_date,IFNULL(b.count,0) AS COUNT
FROM (
SELECT CURDATE() AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS click_date
) a LEFT JOIN (
SELECT DATE(create_time) AS totalMoney, COUNT(id) AS COUNT
FROM sys_user
GROUP BY DATE(create_time)
) b ON a.click_date = b.totalMoney;
假设该表中还有个字段money,要统计当前日期之前一周内每天的数据money的和,模拟数据如下
查询SQL如下
#查询当前日期之前一周内每天的数据money 合计
SELECT a.click_date,IFNULL(b.count,0) AS COUNT
FROM (
SELECT CURDATE() AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS click_date
) a LEFT JOIN (
SELECT DATE(create_time) AS date, SUM(money) AS COUNT
FROM sys_user
GROUP BY DATE(create_time)
) b ON a.click_date = b.date;
查询结果:
以上采用左外连接,若该日期无数据,则显示0,下面还有一种方式
select DATE(create_time) AS click_date, SUM(money) AS COUNT from sys_user where DATE_SUB(NOW(),INTERVAL 7 DAY) < DATE_FORMAT(create_time,'%Y-%m-%d')
GROUP BY DATE(create_time) ORDER BY DATE(create_time) DESC;
查询结果:
后面就会缺少日期和相应的数据,推荐使用第一种左外连接