Mysql中计算表中日期距离当前日期有几个月的数据个数并横向展示
面试中遇到的问题,一张表,tdate 为DATE格式 要求根据表一的数据编写sql得到表二的形式
表一
表二
主要用到mysql中过的TIMESTAMPDIFF()函数和if函数 下面是完整的sql
SELECT t.state, SUM(IF(t.monthnum=0,
1,
0
)
) AS '距离当前时间不到一个月', SUM(IF(t.monthnum=1,
1,
0
)
) AS '距离当前时间一个月', SUM(IF(t.monthnum=2,
1,
0
)
) AS '距离当前时间两个月', SUM(IF(t.monthnum>=3,
1,
0
)
) AS '距离当前时间三个月及以上'
FROM (
SELECT state, TIMESTAMPDIFF(MONTH,tdate, NOW()) AS monthnum
FROM test_date
WHERE state = 1 AND tdate < NOW() UNION ALL
SELECT state, TIMESTAMPDIFF(MONTH,tdate, NOW()) AS monthnum
FROM test_date
WHERE state = 2 AND tdate < NOW()) t
GROUP BY (t.state);
if 函数
IF(expr1,expr2,expr3),如果expr1的值为true,则返回expr2的值,如果expr1的值为false,
因为要计数所以sql中expr2 给值1 每次累加一
TimeStampDiff()函数
是MySQL本身提供的可以计算两个时间间隔的函数,语法为:
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其中unit单位有如下几种,分别是:FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR 。该参数具体释义如下:
FRAC_SECOND 表示间隔是毫秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 星期
MONTH 月
QUARTER 季度
YEAR 年
面试回来之后写的 当时没有答上来,感觉这么写效率会很低 有更好的方案的话 欢迎指正