求大神帮我写一条查询增量的sql语句

图片说明
图片说明

如图,我有这些字段,每天都会插入一些数据
我现在需要获取的内容是
今天的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的就是昨天的
数据