如图,我有这些字段,每天都会插入一些数据
我现在需要获取的内容是
今天的read,click数据,和今天的减昨天的差值,和aid,规则如图
求大神帮我写一个
SELECT
SUM(d.read_num) AS READ,
SUM(d.click_num) AS click,
SUM(d.read_num - t.read_num) AS diffREAD,
SUM(d.click_num - t.click_num) AS diffclick,
u.aid
FROM
tc_url AS u
LEFT JOIN tc_data AS d
ON d.uid = u.id
LEFT JOIN
(SELECT
_d.read_num,
_d.click_num,
_u.aid
FROM
tc_url AS _u
LEFT JOIN tc_data AS _d
ON _d.uid = _u.id
WHERE _u.createtime = '昨天') AS t
ON t.aid = u.aid
WHERE u.createtime = '今天'
GROUP BY u.aid
机器没装SQL,脱手写的,你自己试试
建议楼主在表中增加时间字段,这样你的问题就好解决了:
alter table tab_name add inserttime datetime after aid
哥们,你既然要计算昨天减今天的,你都没有date字段,你该如何来弥补这个问题?要么你在找找其他的表是否有这个字段,要么你就得加一个字段,否则谁都不知道我插入数据时是哪一天的,你说呢
你要求增量么?上星期我真好写过每日报表,是用spring data 做的,动态查询。
第一步:确定时间
// 前日0点
private static Date getLastDay(Date date) {
Calendar cal = Calendar.getInstance();
cal.setTime(date);
cal.add(Calendar.DAY_OF_MONTH, -1);// 前一天
cal.set(Calendar.HOUR_OF_DAY, 0);
cal.set(Calendar.MINUTE, 0);
cal.set(Calendar.SECOND, 0);
cal.set(Calendar.MILLISECOND, 0);
return cal.getTime();
}
// 每日0点前1毫秒
private static Date getNowDay(Date date) {
Calendar cal = Calendar.getInstance();
cal.setTime(date);
cal.set(Calendar.HOUR_OF_DAY, 0);
cal.set(Calendar.MINUTE, 0);
cal.set(Calendar.SECOND, 0);
cal.set(Calendar.MILLISECOND, 0);
return DateUtils.addMilliseconds(cal.getTime(), -1);
}
第二步:动态查询
private List findUserActions() {
List actionList = userActionDao
.findAll(new Specification() {
public Predicate toPredicate(Root root,
CriteriaQuery<?> query, CriteriaBuilder builder) {
List predicates = Lists.newArrayList();
Path expression = root.get("actionTime");
Date beginTime = getLastDay(new Date());
Date endTime = getNowDay(new Date());
predicates.add(builder.between(expression, beginTime,endTime));
// 将所有条件用 and 联合起来
if (!predicates.isEmpty()) {
return builder.and(predicates
.toArray(new Predicate[predicates.size()]));
}
return builder.conjunction();
}
});
return actionList;
}
大概就是这样,剩下的就要根据具体业务去修改。
你写一个今天零点零分的时间A和一个昨天零点零分的时间B用where判断一下,createTime大于等于A的就是今天的数据,大于等于B小于A的就是昨天的
数据