スポンサーリンク

MySQLで，連続した番号や日付を出力する。PostgreSQLのgenerate_series関数と同じことを実現

MySQL

MySQLで，
連続した番号のリストや
連続した日付の一覧を出力するようなSQL。

連続した番号を出力する

SELECT
    0 AS generate_series 
FROM 
    -- MySQL組み込みのダミーテーブル
    -- この場合，FROM句に意味はない
    DUAL 
WHERE 
    -- 変数を初期化
    -- WHERE句内の条件が0になるので
    -- このSELECT文の該当レコードは存在しない
    (@num:=0)*0 

-- この上のSELECT文は，変数を初期化するためにある。
-- この下のSELECT文は，変数をインクリメントしながら複数レコードをスキャンする。
UNION ALL

SELECT 
    -- １レコードごとに結果表示するたびに変数をインクリメント
    @num:=@num+1      
FROM 
    -- レコードが多数存在するテーブルなら何でもよい
    `information_schema`.COLUMNS
LIMIT 
    30
;

実行結果

generate_series 	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

連続した日付を出力する

「連番を出力するSQL」をサブクエリとして
それをもとに，起点となる日付に足し算してゆけばよい。

SELECT
    -- フォーマットされた連続した日付
    date_format(

        -- 日付の足し算を行なう
        date_add(
            -- 起点となる日付
            '2019-08-01', 
            
            -- 起点から何日足すか
            interval t_cnt.cnt day
        ), 
        
        -- 日付情報をフォーマットする
        '%Y-%m-%d'
    ) AS date_series
FROM
    -- 連番を出力するSQL
    (
        SELECT
            0 AS cnt 
        FROM 
            -- MySQL組み込みのダミーテーブル
            -- この場合，FROM句に意味はない
            DUAL 
        WHERE 
            -- 変数を初期化
            -- WHERE句内の条件が0になるので
            -- このSELECT文の該当レコードは存在しない
            (@num:=-1)*0 

        -- この上のSELECT文は，変数を初期化するためにある。
        -- この下のSELECT文は，変数をインクリメントしながら複数レコードをスキャンする。
        UNION ALL

        SELECT 
            -- １レコードごとに結果表示するたびに変数をインクリメント
            @num:=@num+1      
        FROM 
            -- レコードが多数存在するテーブルなら何でもよい
            `information_schema`.COLUMNS
        LIMIT 
            30
    ) as t_cnt
;

出力結果：

date_series 	
2019-08-01
2019-08-02
2019-08-03
2019-08-04
2019-08-05
2019-08-06
2019-08-07
2019-08-08
2019-08-09
2019-08-10
2019-08-11
2019-08-12
2019-08-13
2019-08-14
2019-08-15
2019-08-16
2019-08-17
2019-08-18
2019-08-19
2019-08-20
2019-08-21
2019-08-22
2019-08-23
2019-08-24
2019-08-25
2019-08-26
2019-08-27
2019-08-28
2019-08-29
2019-08-30

今日までの30日間の日付を取得したい場合

上記をちょっと書き換えて
0からデクリメントしていく連番を作成
それを今日の日付に足し算してゆき
最終出力時にはASCでソート。

SELECT
    -- フォーマットされた連続した日付
    date_format(

        -- 日付の足し算を行なう
        date_add(
            -- 起点となる日付
            date(now()), 
            
            -- 起点から何日足すか
            interval t_cnt.cnt day
        ), 
        
        -- 日付情報をフォーマットする
        '%Y-%m-%d'
    ) AS date_series
FROM
    -- 連番を出力するSQL
    (
        SELECT
            0 AS cnt 
        FROM 
            -- MySQL組み込みのダミーテーブル
            -- この場合，FROM句に意味はない
            DUAL 
        WHERE 
            -- 変数を初期化
            -- WHERE句内の条件が0になるので
            -- このSELECT文の該当レコードは存在しない
            (@num:=1)*0 

        -- この上のSELECT文は，変数を初期化するためにある。
        -- この下のSELECT文は，変数をインクリメントしながら複数レコードをスキャンする。
        UNION ALL

        SELECT 
            -- １レコードごとに結果表示するたびに変数をインクリメント
            @num:=@num-1      
        FROM 
            -- レコードが多数存在するテーブルなら何でもよい
            `information_schema`.COLUMNS
        LIMIT 
            30
    ) as t_cnt
ORDER BY
    date_series ASC
；

出力結果（今日は8/19）

date_series 
2019-07-21
2019-07-22
2019-07-23
2019-07-24
2019-07-25
2019-07-26
2019-07-27
2019-07-28
2019-07-29
2019-07-30
2019-07-31
2019-08-01
2019-08-02
2019-08-03
2019-08-04
2019-08-05
2019-08-06
2019-08-07
2019-08-08
2019-08-09
2019-08-10
2019-08-11
2019-08-12
2019-08-13
2019-08-14
2019-08-15
2019-08-16
2019-08-17
2019-08-18
2019-08-19

参考資料

MySQLの場合：

裏MySQLクエリー入門(15) 応用編3 MySQLで連番の仮想表を作成
https://it7c.hatenadiary.org/entry/20100713/1278950305


FROM DUALの意味
https://qiita.com/macoshita/items/e3bd0328cd2e7d4b1bcb

PostgreSQLの場合：

SQLで，テーブルに存在しない複数行のデータを取得する方法　（PostgreSQLの generate_series関数をマスターしよう）
https://language-and-engineering.hatenablog.jp/entry/20100309/p1