核心:
select @num:=@num+1 num from (select @num:=-1) n
定义一个只有一个自增字段表,这张变外联的结果就是生成一个自增序列,起到辅助作用
组合使用效果:
SQL:
select @num:=@num+1 num,name from (select @num:=-1) n,user_area where @num < 10
配合其他表的查询,生成一条自增序列,这个属于是基础运用了
对日期使用的原理其实一样,主要是配合mysql自带的日期函数,获取自己想要的
对日期的处理
案列1.获取本年所有的月份
SELECT
@num:=@num+1 num,
DATE_FORMAT(DATE_ADD((concat(year(now()),'-01-01')),INTERVAL @num MONTH),'%Y-%m') as xx_date
FROM
mysql.help_topic a,
(select @num:=-1) n
WHERE
@num < 11
解析:
now() 获取当前日期,包括时分秒
year(now() 获取当前日期的年份
concat(year(now()),’-01-01’)用年份拼接字符串得到本年的第一个月
DATE_ADD((concat(year(now()),’-01-01’)),INTERVAL @num MONTH) 根据序列的数字对日期进行加法运算,单位是月
DATE_FORMAT(DATE_ADD((concat(year(now()),’-01-01’)),INTERVAL @num MONTH),’%Y-%m’)
把年月日的形式转换为年月的形式,具体的看自己的需求
select @num:=-1 序列初始值 -1
@num:=@num+1 序列每次加1
@num < 11 一年有12个月,如果是今年一直到当前月,那就把这个11换成当前月减1,month(now)-1
案例2.获取本月所有日期
SELECT
@num:=@num+1 num,
DATE_ADD(DATE_FORMAT((DATE_ADD(curdate(),interval -day(curdate())+1 day)), '%Y-%m-%d'),INTERVAL @num DAY) as d_date
FROM
mysql.help_topic a,
(select @num:=-1) n
WHERE
@num < (SELECT DAYOFMONTH(LAST_DAY(date(now()))) - 1)
套路都一样,先获取本月的第一天的日期,然后根据本月最后一天的日期获取本月的天数
ps: 这里面的DATE_FORMAT(XXXXX,’%Y-%m-%d’)可以换成DATE(XXXXX),如果是本月到当前日期,把where后面的条件换成day(now())-1
案例3.获取当天所有小时日期
SELECT DATE_FORMAT( DATE_SUB( DATE_FORMAT( NOW(),'%Y-%m-%d'),INTERVAL ( -(@i:=@i+1) ) HOUR ) ,'%Y-%m-%d %H') 'time'
FROM (
SELECT a FROM
(SELECT '1' a UNION SELECT '2' UNION SELECT '3'UNION SELECT '4' ) a
JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) b
ON true
) b ,(SELECT @i:=-1) i
解析:
SELECT a FROM
(SELECT '1' a UNION SELECT '2' UNION SELECT '3'UNION SELECT '4' ) a
JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) b
ON true
的作用是获取24行的一个递增序列,用来处理日期,这个是用来控制数量的,如果要是12小时,就把1234改成12就行了
ps:如果要想获得今天截止当前日期,加个where判断一下就行了,通过控制@i:=@i+1来控制时间的间隔
案例4.获取过去七天
select
@num:=@num+1 num,
DATE_ADD(DATE_FORMAT((DATE_ADD(curdate(),interval -6 day)), '%Y-%m-%d'),INTERVAL @num DAY) as d_date
FROM
mysql.help_topic a,
(select @num:=-1) n
WHERE
@num < 6
案例4.关于日期的综合使用,获取这些各种时间段的日期到底有什么用:
举例:在关于日期group by之后,null的部分丢失,拿不到的不展示
select a.‘date’ date ,case when b.Tcount is null then 0 else b.Tcount end Tcount,case when c.Acount is null then 0 else c.Acount end Acount from
(SELECT @s :=@s + 1 as ‘index’, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS ‘date’ FROM mysql.help_topic,(SELECT @s := -1) temp WHERE @s < 6 ORDER BY ‘date’
) a
left join
(select count(1) Tcount,Date(createtime) time from focalStaffmag where states != 3 and gzreason != 2 GROUP BY time
) b
on a.‘date’ = b.time
left join
(select count(1) Acount,Date(createtime) time from focalStaffmag where states != 3 and gzreason != 1 GROUP BY time
) c on a.‘date’ = c.time
让日期连续的输出,不存在的部分补0;
用连续的日期联表你groupby的统计表,然后获取连续的日期和统计的结果,再把日期没匹配到的case when成一个你想要的值,这样,一个日期连续的统计就完成了
总结:把复杂的问题拆分出来,变成各个简单的小问题再逐个击破,sql就那么回事。