一、DQL(查询)
1-1】计算回复时间
# 例如:三天前回复了我,我却把他搁置了
CASE
WHEN TIMESTAMPDIFF(MINUTE, b.create_date, now()) < 5 THEN '刚刚'
WHEN TIMESTAMPDIFF(MINUTE, b.create_date, now()) < 60 THEN CONCAT(TIMESTAMPDIFF(MINUTE, b.create_date, now()),'分钟前')
WHEN TIMESTAMPDIFF(HOUR, b.create_date, now()) < 24 THEN CONCAT(TIMESTAMPDIFF(HOUR, b.create_date, now()),'小时前')
WHEN TIMESTAMPDIFF(DAY, b.create_date, now()) < 31 THEN CONCAT(TIMESTAMPDIFF(DAY, b.create_date, now()),'天前')
WHEN TIMESTAMPDIFF(MONTH, b.create_date, now()) < 12 THEN CONCAT(TIMESTAMPDIFF(MONTH, b.create_date, now()),'月前')
ELSE CONCAT(TIMESTAMPDIFF(YEAR, b.create_date, now()),'年前')
END as time,
1-2】最近几个时间阶段虚拟表
# 例如查询近七天,近 12 个月的数据
SELECT
DATE_FORMAT(a.t,'%Y-%c') as time,
COUNT(t.id) as sum
FROM
(
SELECT now() as t union
SELECT DATE_SUB(now(), INTERVAL + 1 MONTH) as t union
SELECT DATE_SUB(now(), INTERVAL + 2 MONTH) as t union
SELECT DATE_SUB(now(), INTERVAL + 3 MONTH) as t union
SELECT DATE_SUB(now(), INTERVAL + 4 MONTH) as t union
SELECT DATE_SUB(now(), INTERVAL + 5 MONTH) as t union
SELECT DATE_SUB(now(), INTERVAL + 6 MONTH) as t union
SELECT DATE_SUB(now(), INTERVAL + 7 MONTH) as t union
SELECT DATE_SUB(now(), INTERVAL + 8 MONTH) as t union
SELECT DATE_SUB(now(), INTERVAL + 9 MONTH) as t union
SELECT DATE_SUB(now(), INTERVAL + 10 MONTH) as t union
SELECT DATE_SUB(now(), INTERVAL + 11 MONTH) as t
) a
LEFT JOIN table t
ON MONTH(t.create_date) = MONTH(a.t)
AND TIMESTAMPDIFF(YEAR, t.create_date, now()) < 1
AND IF(MONTH(t.create_date)=MONTH(NOW()), YEAR(t.create_date)=YEAR(NOW()), 1=1)
GROUP BY MONTH(a.t)
ORDER BY a.t
1-3】两个日期间的数据统计
BETWEEN 也许会更好
// 使用 java 代码帮助实现,这边为工具类下的方法
/**
* 获取两个日期中所有天
*
* @param startTime 开始时间
* @param endTime 结束时间
* @param include 是否包含结束时间
* @return List<Date> 格式 +2:[ ]、[ )
*/
public static List<Date> betweenDayInclude(Date startTime, Date endTime, Boolean include) {
List<Date> returnList = new ArrayList<>();
Calendar start = Calendar.getInstance();
start.setTime(startTime);
Calendar end = Calendar.getInstance();
end.setTime(endTime);
/*
避免出现 开始时间 为当天日出的同时 结束日间 为日落时分
第一阶段:判断出不是同一天,是过去的天 ✓
第二阶段:同一天进行判断 开始时间 依旧是 结束时间的前面 ✗
总结:这是个 bug,得排除
*/
end.set(end.get(Calendar.YEAR), end.get(Calendar.MONTH), end.get(Calendar.DATE), 0, 0, 0);
while (start.before(end)) {
returnList.add(start.getTime());
start.add(Calendar.DAY_OF_YEAR, 1);
}
if (Boolean.TRUE.equals(include)) {
returnList.add(end.getTime());
}
return returnList;
}
SELECT
DATE_FORMAT(b.t,'%m.%e') as time,
b.*
from
(
<foreach item="l" collection="list" open="" separator=" union " close="">
select #{l} as t
</foreach>
) a
left join table_b b on TIMESTAMPDIFF(DAY, b.create_date, a.t) = 0
GROUP BY DAY(a.t)
order by DAY(a.t)