一.统计五个没有关联的表的个数
(1)比如说有五张表分别为: t_grad, t_family, t_worker,t_teacher,t_class分别10条,15条,20条,10条,10条数据
SELECT SUM(COUNT) number FROM
(
SELECT COUNT(*) COUNT FROM t_grad
UNION ALL
SELECT COUNT(*) COUNT FROM t_family
UNION ALL
SELECT COUNT(*) COUNT FROM t_worker
UNION ALL
SELECT COUNT(*) COUNT FROM t_teacher
UNION ALL
SELECT COUNT(*) COUNT FROM t_student stu WHERE stu.delete_status = ‘1’
UNION ALL
SELECT COUNT(*) COUNT FROM t_class
)action
最后得出来的为65条数据,如果需要什么可以直接在where里面加上条件,例如:
SELECT COUNT(*) COUNT FROM t_student stu WHERE stu.delete_status = ‘1’
二.统计24小时之内没有数据上传的个数
(1)先判断全部的数据的id
SELECT DISTINCT cla.class_id
FROM t_class cla
LEFT JOIN t_student stu on stu.class_id = cla.student_id and stu.pen_type = ’ DevType01’
LEFT JOIN t_teacher tea ON cla.class_id = tea.class_id
得出来的结果为13个id
(2)判断时间在24小时之内没有上传数据的id
SELECT DISTINCT cla.class_id
FROM t_class cla
LEFT JOIN t_student stu on stu.class_id = cla.student_id and stu.pen_type = ’ DevType01’
LEFT JOIN t_teacher tea ON cla.class_id = tea.class_id
WHERE ABS(TIMESTAMPDIFF(HOUR,cla.update_time,(SELECT date_format(DATE_ADD(NOW(),INTERVAL 53 MINUTE),‘%Y-%m-%d %H:%i:%s’))))<24 AND cla.DELETE_STATUS =‘1’
得出来的结果是一个数33
(3)最后统计24小时之内没有数据上传的个数
SELECT count(DISTINCT cla.class_id) num1
FROM t_class cla
LEFT JOIN t_student stu on stu.class_id = cla.student_id and stu.pen_type = ’ DevType01’
LEFT JOIN t_teacher tea ON cla.class_id = tea.class_id
WHERE ABS(TIMESTAMPDIFF(HOUR,cla.update_time,(SELECT date_format(DATE_ADD(NOW(),INTERVAL 53 MINUTE),‘%Y-%m-%d %H:%i:%s’))))<24 AND cla.DELETE_STATUS =‘1’
最后得出来的数据统计结果就是1个