1年分の年月日を生成する場合、大抵はExcelを使ったり、プログラムを作って生成しまう場合が多い。
この1年分の年月日をSQLで生成生成する場合は、以下のようなSQL文を発行すれば生成できる。
/*1年分の年月日生成*/
select date_format(date_add('20100101', interval(number) day), '%Y%m%d') as yyyymmdd
from (
select hundred.n + tens.n + ones.n as number
from (
select 0 as n union all
select 1 as n union all
select 2 as n union all
select 3 as n union all
select 4 as n union all
select 5 as n union all
select 6 as n union all
select 7 as n union all
select 8 as n union all
select 9 as n
) as ones cross join (
select 0 as n union all
select 10 as n union all
select 20 as n union all
select 30 as n union all
select 40 as n union all
select 50 as n union all
select 60 as n union all
select 70 as n union all
select 80 as n union all
select 90 as n
) as tens cross join (
select 0 as n union all
select 100 as n union all
select 200 as n union all
select 300 as n
) as hundred
) as dummy
where date_format(date_add('20100101', interval(number) day), '%Y%m%d') < '20110101'
order by yyyymmdd asc;
データベースに接続できる環境ならば、上記のSQL文をプログラムを書くよりも早いかもしれない。
1件のコメント
コメントは受け付けていません。