mysql查询今日数据和查询多表提交总数

今天有个业务是查询每日提交的总数,并返回count,结合sql函数做了下,如果要提高速度还得加索引

select Count_All.* from 
(select tu.uid,tu.uavatar,tu.uname,ts.create_time,ts.suid,ts.todaysummary,ts.tomorrowsummary
from t_user tu
right join t_summary ts on tu.uid = ts.uid
AND DATEDIFF(ts.create_time, NOW()) = 0
where tu.uid = 2) as Count_All

使用内查询来给外部设字段返回,后端java建相关vo类来返回实体信息

如果想要进行多表连接查询多张表的提交总数,使用UNION ALL来连接不同表的总数

SELECT SUM(total.total_count) AS total_sum FROM (SELECT count(*) as total_count FROM t_user tu 
right join t_consumablematerial c1 
on c1.uid = tu.uid AND 
DATE-DIFF(c1.create_time, NOW()) = 0 where tu.uid = '2' 
UNION ALL 
SELECT count(*) as total_count FROM t_user tu 
right join t_equipmentinthe e1 on e1.uid = tu.uid 
AND DATE-DIFF(e1.create_time, NOW()) = 0 where tu.uid = '2') AS total

DATEDIFF(ts.create_time, NOW()) = 0 DATEDIFF(create_time, NOW())计算create_time和当前日期之间的天数差异，如果结果为0，则表示create_time为今天的日期。