奇怪的 SQL

---

一、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)