- 连续时间mysql代码
select DATE_FORMAT(date_add(NOW(),interval @i:=@i+1 MONTH),'%Y-%m') as date
from (
select 1
union all select 1
union all select 1
) as tmp,
(select @i:= -1) t
分析:
1.1 这里演示月份,可以将月份改为天
1.2 select 1 union all select 1 union all select 1 union all select 1 是为了生成一个一列N行的虚拟表,然后由表t与其做笛卡尔积,这样根据N行会生成n行的一个时间序列。
- 连续时间mybatis代码
select DATE_FORMAT(date_add(#{startDate},interval @i:=@i+1 MONTH),'%Y-%m') as date
from (
select 1
<foreach item="index" collection="countArr">
union all select 1
</foreach>
) as tmp,
(select @i:= -1) t
order by date desc
分析:
2.1 startDate 起始时间
2.2 countArr 一个起始时间和结束时间差的集合 例:如下代码
public String[] calcBetweenDate(String start, String end) {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM");
Calendar startDate = Calendar.getInstance();
Calendar endDate = Calendar.getInstance();
try {
startDate.setTime(df.parse(start));
endDate.setTime(df.parse(end));
} catch (Exception e) {
System.out.println("日期转换出错");
}
int count = endDate.get(Calendar.MONTH) - startDate.get(Calendar.MONTH);
String[] countArr = new String[count];
return countArr;
}
- 连续数字mysql代码
SELECT @i:=@i+1 as number from
(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) tmp,
--(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) tmp,
(SELECT @i:=0) t
分析:
3.1:代码中注释了一个,如果有2个就是乘法 即:3*3=9