VB.NET-TIPS などプログラミングについて

2024/02/06

MySQL データのグループ化（集計処理）について(GROUP BY句、WITH ROLLUP)

MySQL だけでは無く、他のデータベースでもデータをあるキーに従ってグループ化を行って、「合計値」「平均値」「計数値」の集計処理を行うことがあります。

あるデータをその中のどれかのカラムを指定して GROUP BY の後ろにそれを指定します。データの「合計値」「平均値」「計数値」はそれぞれ SUM、AVG、COUNT の関数を使います。

グループ化は以下の様な構文で指定します。

SELECT カラム名1 [, カラム名2, ...] ,[集計関数...]  FROM テーブル名...
GROUP BY カラム名1 [, カラム名2, ...]

GROUP BY の後ろに指定されたカラム名の値が同じデータのグループ化を行います。複数のカラム名が指定された場合は、それぞれのカラム名の値が同じものでグループ化を行います。

今回テストで用いるテーブルは、以下の記事のものを少し変更しています。
⇒MySQL マスタテーブルの作成と参照に関する考察について(CREATE TABLE、AUTOINCREMENT、JOIN、LEFT JOIN)

以下の内容で、順を追って説明します。

テストテーブル（商品マスタ、売上データ）の作成について
GROUP BY 指定のカラムが1個の場合ついて
GROUP BY 指定のカラムが複数の場合ついて
グループ化した結果をさらに集計する方法ついて(WITH ROLLUP)

■テストテーブル（商品マスタ、売上データ）の作成について

テストデータとして「商品情報」と「売上データ」のテーブルを使い、テーブルの連結を行いながら、グループ化について説明したいと思います。
「商品情報」（商品マスタと言い換えます）は以下の様な項目が必要だと思います。

商品コード（商品の型番？）
商品分類
商品名
単価

「売上データ」は以下の様な項目が必要だと思います。ここでは、誰が買ったかなどの情報は割愛して考えますので、商品と売上のみの情報の項目のみとしています。（後、消費税についても割愛します。）

売上NO
商品コード
日付
数量
単価（単価の変更があるため）
金額

上記の項目でテーブル作成SQLを考えますが、商品マスタの場合は「商品コード」を主キーにしてもいいのですが、 AUTO_INCREMENT な「id」を追加してこちらを主キーとします。
「商品コード」とマスタの識別子となる「id」を分けた方が、後々なにかと便利になるといいますか、 MySQL の作法的に私は「id」を付加しています。（テーブルカラムの指定で AUTO_INCREMENT があるので気軽に連番のデータが付加できるとも言えますが...）

なお、「商品マスタ」「売上データ」ともに削除フラグ（デフォルトで「0」設定）を持たせました。それぞれのデータの削除処理の時にフラグを「1」に更新する様に考えています。こうすれば廃品種となったマスタのデータを残しておくことができます。

作成の SQL は以下の様になります。（「OR REPLACE」の指定は置き換えを行います）

-- 商品マスタ
CREATE OR REPLACE TABLE tm_prod (
	id    INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    code  VARCHAR(32),	-- 商品コード
    class VARCHAR(32),	-- 商品分類
    name  VARCHAR(32),	-- 商品名
    price INT,          -- 単価
	del	  INT DEFAULT 0 -- 削除フラグ:0(存在),1:(削除)
);

-- 売上データ
CREATE OR REPLACE TABLE tt_sales (
    no        INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 売上NO
    id_prd    INT,      -- 商品マスタ.id
    salesdate DATE,	    -- 日付
    volume    INT,      -- 数量
    price     INT,      -- 単価
    amount    INT,      -- 金額
	del	  INT DEFAULT 0 -- 削除フラグ:0(存在),1:(削除)
);

それでは、上記のテーブルに以下の SQL でテストデータを登録します。

-- 商品テーブル
INSERT INTO tm_prod(code, class, name, price) VALUES
    ('pen01', 'pen', '鉛筆HB', 80), 
    ('pen02', 'pen', '鉛筆B' , 100),
    ('pen03', 'pen', '鉛筆B2' , 100),
    ('cre01', 'cre', 'くれよん12色', 1000),
    ('cre02', 'cre', 'くれよん24色', 2000),
    ('pen10', 'kpen','蛍光ペン', 120),
    ('pen11', 'kpen','蛍光ペン2', 130),
    ('era01', 'era', '消しゴム', 150),
    ('era02', 'era', '消しゴム2', 200);
-- 売上件数テーブル
INSERT INTO tt_sales(id_prd, salesdate, volume, price, amount) VALUES
    (1, '2024-01-04', 10,   80,  800), 
    (4, '2024-01-04',  1, 1000, 1000), 
    (6, '2024-01-04',  2,  120,  240), 
    (7, '2024-01-05',  3,  130,  390), 
    (1, '2024-01-07', 15,   80, 1200), 
    (2, '2024-01-07', 10,  100, 1000), 
    (5, '2024-01-07',  2, 2000, 4000), 
    (1, '2024-01-05', 20,   80, 1600), 
    (3, '2024-01-05',  1,  100,  100), 
    (4, '2024-01-10',  2, 1000, 2000), 
    (2, '2024-01-15',  2,  100,  200), 
    (6, '2024-01-05',  5,  120,  600), 
    (7, '2024-01-14',  3,  130,  390), 
    (8, '2024-01-20',  1,  150,  150), 
    (5, '2024-01-20',  4, 2000, 8000), 
    (8, '2024-01-22', 10,  150, 1500), 
    (9, '2024-01-22', 10,  200, 2000);

テストデータを確認します。

MariaDB [test]> select * from tm_prod;
+----+-------+-------+--------------+-------+------+
| id | code  | class | name         | price | del  |
+----+-------+-------+--------------+-------+------+
|  1 | pen01 | pen   | 鉛筆HB       |    80 |    0 |
|  2 | pen02 | pen   | 鉛筆B        |   100 |    0 |
|  3 | pen03 | pen   | 鉛筆B2       |   100 |    0 |
|  4 | cre01 | cre   | くれよん12色 |  1000 |    0 |
|  5 | cre02 | cre   | くれよん24色 |  2000 |    0 |
|  6 | pen10 | kpen  | 蛍光ペン     |   120 |    0 |
|  7 | pen11 | kpen  | 蛍光ペン2    |   130 |    0 |
|  8 | era01 | era   | 消しゴム     |   150 |    0 |
|  9 | era02 | era   | 消しゴム2    |   200 |    0 |
+----+-------+-------+--------------+-------+------+
9 rows in set (0.00 sec)

MariaDB [test]> select * from tt_sales;
+----+--------+------------+--------+-------+--------+------+
| no | id_prd | salesdate  | volume | price | amount | del  |
+----+--------+------------+--------+-------+--------+------+
|  1 |      1 | 2024-01-04 |     10 |    80 |    800 |    0 |
|  2 |      4 | 2024-01-04 |      1 |  1000 |   1000 |    0 |
|  3 |      6 | 2024-01-04 |      2 |   120 |    240 |    0 |
|  4 |      7 | 2024-01-05 |      3 |   130 |    390 |    0 |
|  5 |      1 | 2024-01-07 |     15 |    80 |   1200 |    0 |
|  6 |      2 | 2024-01-07 |     10 |   100 |   1000 |    0 |
|  7 |      5 | 2024-01-07 |      2 |  2000 |   4000 |    0 |
|  8 |      1 | 2024-01-05 |     20 |    80 |   1600 |    0 |
|  9 |      3 | 2024-01-05 |      1 |   100 |    100 |    0 |
| 10 |      4 | 2024-01-10 |      2 |  1000 |   2000 |    0 |
| 11 |      2 | 2024-01-15 |      2 |   100 |    200 |    0 |
| 12 |      6 | 2024-01-05 |      5 |   120 |    600 |    0 |
| 13 |      7 | 2024-01-14 |      3 |   130 |    390 |    0 |
| 14 |      8 | 2024-01-20 |      1 |   150 |    150 |    0 |
| 15 |      5 | 2024-01-20 |      4 |  2000 |   8000 |    0 |
| 16 |      8 | 2024-01-22 |     10 |   150 |   1500 |    0 |
| 17 |      9 | 2024-01-22 |     10 |   200 |   2000 |    0 |
+----+--------+------------+--------+-------+--------+------+
17 rows in set (0.00 sec)

■GROUP BY 指定のカラムが1個の場合ついて

それでは売上データに対してグループ化を行う為に GROUP BY に1個のカラムとして「商品分類」を指定し、「商品分類」ごとの商品の売上数と売上金額の合計数を表示する SQL を示します。
「商品分類」は「商品マスタ」に持っているため、「売上データ」と「商品マスタ」を FROM句 で INNER JOIN で結合します。
結合によりどちらかのテーブルにあるカラムを1個のテーブルにあるカラムの様に考えて指定ができます。

SELECT 
    tm_prod.class AS '商品分類',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY tm_prod.class
ORDER BY tm_prod.class;

この SQL の結果は以下の通りです。

+----------+------+-------+
| 商品分類 | 数量 | 金額  |
+----------+------+-------+
| cre      |    9 | 15000 |
| era      |   21 |  3650 |
| kpen     |   13 |  1620 |
| pen      |   58 |  4900 |
+----------+------+-------+
4 rows in set (0.00 sec)

「売上データ」には「商品分類」の4種類のデータが在ることが分かります。

■GROUP BY 指定のカラムが複数の場合ついて

グループ化の指定が複数の場合の例を示します。「商品マスタ」は「商品分類」ごとに分類分けして登録されていますので、「商品分類」「商品コード」の2つを GROUP BY のカラムに指定します。

先ほどの1カラム指定のSQLに追加を行います。

SELECT 
    tm_prod.class AS '商品分類',
    tm_prod.code  AS '商品コード',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY 
    tm_prod.class, 
    tm_prod.code 
ORDER BY
    tm_prod.class, 
    tm_prod.code;

SQL の実行結果は以下の通りです。

+----------+------------+------+-------+
| 商品分類 | 商品コード | 数量 | 金額  |
+----------+------------+------+-------+
| cre      | cre01      |    3 |  3000 |
| cre      | cre02      |    6 | 12000 |
| era      | era01      |   11 |  1650 |
| era      | era02      |   10 |  2000 |
| kpen     | pen10      |    7 |   840 |
| kpen     | pen11      |    6 |   780 |
| pen      | pen01      |   45 |  3600 |
| pen      | pen02      |   12 |  1200 |
| pen      | pen03      |    1 |   100 |
+----------+------------+------+-------+
9 rows in set (0.00 sec)

■グループ化した結果をさらに集計する方法ついて(WITH ROLLUP)

GROUP BY の指定であるカラムに対する集計処理は行えますが、その集計結果をさらに集計する場合には GROUP BY の最後に WITH ROLLUP の指定を行います。

上記の「■GROUP BY 指定のカラムが1個の場合ついて」での SQL について WITH ROLLUP の指定を行います。

SELECT 
    tm_prod.class AS '商品分類',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY tm_prod.class WITH ROLLUP
ORDER BY tm_prod.class;

これを実行すると以下の様なエラーが表示されました。

MariaDB [test]> SELECT
    ->     tm_prod.class AS '商品分類',
    ->     SUM(tt_sales.volume) AS '数量',
    ->     SUM(tt_sales.amount) AS '金額'
    -> FROM tt_sales
    ->     INNER JOIN tm_prod
    ->     ON  tm_prod.id = tt_sales.id_prd
    ->     AND tm_prod.del = 0
    -> GROUP BY tm_prod.class WITH ROLLUP
    -> ORDER BY tm_prod.class WITH ROLLUP;
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY
MariaDB [test]>

WITH ROLLUP の指定と ORDER BY の指定は同時にはできない様です。
ORDER BY の指定を省いて再度実行します。

MariaDB [test]> SELECT
    ->     tm_prod.class AS '商品分類',
    ->     SUM(tt_sales.volume) AS '数量',
    ->     SUM(tt_sales.amount) AS '金額'
    -> FROM tt_sales
    ->     INNER JOIN tm_prod
    ->     ON  tm_prod.id = tt_sales.id_prd
    ->     AND tm_prod.del = 0
    -> GROUP BY tm_prod.class WITH ROLLUP;
+----------+------+-------+
| 商品分類 | 数量 | 金額  |
+----------+------+-------+
| cre      |    9 | 15000 |
| era      |   21 |  3650 |
| kpen     |   13 |  1620 |
| pen      |   58 |  4900 |
| NULL     |  101 | 25170 |
+----------+------+-------+
5 rows in set (0.00 sec)

結果リストの最終行に「商品分類」が「NULL」の行が表示され、「数量」「金額」の合計値が表示されます。

さらに「■GROUP BY 指定のカラムが複数の場合ついて」での SQL について WITH ROLLUP の指定を行います。

SELECT 
    tm_prod.class AS '商品分類',
    tm_prod.code  AS '商品コード',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY 
    tm_prod.class, 
    tm_prod.code 
WITH ROLLUP;

このSQLの実行結果は以下の様になります。

+----------+------------+------+-------+
| 商品分類 | 商品コード | 数量 | 金額  |
+----------+------------+------+-------+
| cre      | cre01      |    3 |  3000 |
| cre      | cre02      |    6 | 12000 |
| cre      | NULL       |    9 | 15000 |
| era      | era01      |   11 |  1650 |
| era      | era02      |   10 |  2000 |
| era      | NULL       |   21 |  3650 |
| kpen     | pen10      |    7 |   840 |
| kpen     | pen11      |    6 |   780 |
| kpen     | NULL       |   13 |  1620 |
| pen      | pen01      |   45 |  3600 |
| pen      | pen02      |   12 |  1200 |
| pen      | pen03      |    1 |   100 |
| pen      | NULL       |   58 |  4900 |
| NULL     | NULL       |  101 | 25170 |
+----------+------------+------+-------+
14 rows in set (0.00 sec)

各「商品分類」ごとの集計値が「商品コード」が「NULL」の行に表示され、最終行に全ての合計が「商品分類」「商品コード」が共に「NULL」の行に表示されます。

MySQL
0CM

2024/02/02

MySQL SELECT文の連結テーブルの有り無しの指定ついて(SELECT WHERE EXISTS)

MySQL のSELECT文ではあるテーブルをメインとして扱い、それに必要なテーブルを連結して一覧データを取得します。

連結するテーブルが存在するかどうかを条件に含める場合は EXISTS を使います。
また、テーブルが存在しない場合は NOT EXISTS を使います。

それでは以下の記事にありましたが、売上データの一覧リストの取得を行う SELECT文を参考にして EXISTS を考えてみます。
⇒MySQL マスタテーブルの作成と参照に関する考察について(CREATE TABLE、AUTOINCREMENT、JOIN、LEFT JOIN)

■売上データの一覧リストの取得について

以下の SELECT文は商品マスタの削除フラグに関わらず、売上テーブルの一覧を取得しています。
なお、商品マスタの削除フラグを表示する様にしています。

SELECT 
    tt_sales.no AS '売上no',
    tt_sales.id_prd AS '商品id',
    tm_prod.code AS '商品コード',
    tm_prod.name AS '商品名',
    tm_prod.del AS '削除フラグ',
    tt_sales.volume AS '数量',
    tt_sales.price AS '単価',
    tt_sales.amount AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
ORDER BY tt_sales.no;

この SQL の結果は以下の通りです。
売上テーブルと商品マスタは「商品id」のみの結合のため、削除フラグに関わらず一覧表示されます。

+--------+--------+------------+--------------+------------+------+------+------+
| 売上no | 商品id | 商品コード | 商品名       | 削除フラグ | 数量 | 単価 | 金額 |
+--------+--------+------------+--------------+------------+------+------+------+
|      1 |      1 | pen01      | 鉛筆HB       |          0 |   10 |   80 |  800 |
|      2 |      4 | cre01      | くれよん12色 |          1 |    1 | 1000 | 1000 |
|      3 |      6 | pen10      | 蛍光ペン     |          0 |    2 |  120 |  240 |
|      4 |      7 | pen11      | 蛍光ペン2    |          1 |    3 |  130 |  390 |
|      5 |      1 | pen01      | 鉛筆HB       |          0 |   15 |   80 | 1200 |
|      6 |      2 | pen02      | 鉛筆B        |          0 |   10 |  100 | 1000 |
|      7 |      5 | cre02      | くれよん24色 |          0 |    2 | 2000 | 4000 |
|      8 |      1 | pen01      | 鉛筆HB       |          0 |   20 |   80 | 1600 |
|      9 |      3 | pen03      | 鉛筆B2       |          0 |    1 |  100 |  100 |
|     10 |      4 | cre01      | くれよん12色 |          1 |    2 | 1000 | 2000 |
|     11 |      2 | pen02      | 鉛筆B        |          0 |    2 |  100 |  200 |
|     12 |      6 | pen10      | 蛍光ペン     |          0 |    5 |  120 |  600 |
|     13 |      7 | pen11      | 蛍光ペン2    |          1 |    3 |  130 |  390 |
|     14 |      8 | era01      | 消しゴム     |          0 |    1 |  150 |  150 |
|     15 |      5 | cre02      | くれよん24色 |          0 |    4 | 2000 | 8000 |
|     16 |      8 | era01      | 消しゴム     |          0 |   10 |  150 | 1500 |
|     17 |      9 | era02      | 消しゴム2    |          0 |   10 |  200 | 2000 |
+--------+--------+------------+--------------+------------+------+------+------+
17 rows in set (0.00 sec)

■EXISTSを使った一覧リストの取得について

上記の SELECT文に EXISTS を使って削除フラグの条件付けを行います。
なお EXISTSの書き方は以下の様に WHERE の中で記述します。 EXISTS の括弧"(...)"で囲まれた部分は条件付けとなるサブクエリを記述します。このサブクエリが少なくとも1行を返す場合に「true」で、返さない場合は「false」となります。

SELECT [table1.カラムリスト]
FROM table1
WHERE EXISTS (
	SELECT * FROM table2
)

先ほどの一覧SQLで商品マスタの「削除フラグ = 0」の場合（商品マスタが有効）、売上テーブルの内容を表示する SQL を以下に記します。
以下の SQL ですが EXISTS の説明のため、リストの項目に商品マスタのカラムは参照していません。

SELECT 
    tt_sales.no AS '売上no',
    tt_sales.id_prd AS '商品id',
    tt_sales.volume AS '数量',
    tt_sales.price AS '単価',
    tt_sales.amount AS '金額'
FROM tt_sales
WHERE EXISTS ( -- 削除フラグが[0]の場合[true]
	SELECT 1 FROM tm_prod
    WHERE tm_prod.id = tt_sales.id_prd
    AND   tm_prod.del = 0
)
ORDER BY tt_sales.no;

EXISTS のサブクエリで SELECT 1 と記述していますが、この SELECT の中身は何でもよく SELECT * でも SELECT '1' でもよく MySQL では無視される様です。
この SQL の実行結果は以下の通りです。

+--------+--------+------+------+------+
| 売上no | 商品id | 数量 | 単価 | 金額 |
+--------+--------+------+------+------+
|      1 |      1 |   10 |   80 |  800 |
|      3 |      6 |    2 |  120 |  240 |
|      5 |      1 |   15 |   80 | 1200 |
|      6 |      2 |   10 |  100 | 1000 |
|      7 |      5 |    2 | 2000 | 4000 |
|      8 |      1 |   20 |   80 | 1600 |
|      9 |      3 |    1 |  100 |  100 |
|     11 |      2 |    2 |  100 |  200 |
|     12 |      6 |    5 |  120 |  600 |
|     14 |      8 |    1 |  150 |  150 |
|     15 |      5 |    4 | 2000 | 8000 |
|     16 |      8 |   10 |  150 | 1500 |
|     17 |      9 |   10 |  200 | 2000 |
+--------+--------+------+------+------+
13 rows in set (0.00 sec)

商品マスタを連結して商品名などを表示する SQL は以下の様になります。

SELECT 
    tt_sales.no AS '売上no',
    tt_sales.id_prd AS '商品id',
    tm_prod.code AS '商品コード',
    tm_prod.name AS '商品名',
    tm_prod.del AS '削除フラグ',
    tt_sales.volume AS '数量',
    tt_sales.price AS '単価',
    tt_sales.amount AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
WHERE EXISTS (
	SELECT 1 FROM tm_prod
    WHERE tm_prod.id = tt_sales.id_prd
    AND   tm_prod.del = 0
)
ORDER BY tt_sales.no;

■NOT EXISTSを使った一覧リストの取得について

上記の EXISTS の部分を NOT EXISTS とすればリストされる条件が反転されて、削除済みの商品マスタの売上テーブルのデータが表示されます。

SELECT 
    tt_sales.no AS '売上no',
    tt_sales.id_prd AS '商品id',
    tt_sales.volume AS '数量',
    tt_sales.price AS '単価',
    tt_sales.amount AS '金額'
FROM tt_sales
WHERE NOT EXISTS ( -- 削除フラグが[1]の場合[true]
	SELECT * FROM tm_prod
    WHERE tm_prod.id = tt_sales.id_prd
    AND   tm_prod.del = 0
)
ORDER BY tt_sales.no;

結果は以下の様になります。削除された商品マスタの売上データのみ表示されます。

+--------+--------+------+------+------+
| 売上no | 商品id | 数量 | 単価 | 金額 |
+--------+--------+------+------+------+
|      2 |      4 |    1 | 1000 | 1000 |
|      4 |      7 |    3 |  130 |  390 |
|     10 |      4 |    2 | 1000 | 2000 |
|     13 |      7 |    3 |  130 |  390 |
+--------+--------+------+------+------+
4 rows in set (0.16 sec)

EXISTS句 ではサブクエリーの書き方ではより複雑な条件付けができると思います。

MySQL
0CM

2024/01/29

MySQL マスタテーブルの作成と参照に関する考察について(CREATE TABLE、AUTOINCREMENT、JOIN、LEFT JOIN)

仕事では MySQL を使用したシステムに携わることが多く、Web系の仕事の場合にはほぼ100％に近い感じです。

MySQL は Oracle社 が開発したもので現在もメンテナンスがなされている様です。また、Yahoo や Google などのシステムにも採用されていることからも、安心感もあります。

MySQL について20本ぐらいの記事を書いてきましたが、関数やSELECTなどについて個別の事例に付いてが多かったですが、今回は少し毛色を変えて、初心者の方がテーブルの考え方で少参考になりそうな内容を記してみます。

以下の内容で、順を追って説明します。

テストテーブル（商品マスタ、売上データ）の作成について
売上データの一覧リストの取得について
商品マスタが削除済みの場合の売上データの一覧リストの取得について

■テストテーブル（商品マスタ、売上データ）の作成について

いろんなWeb上のシステムがありますが、データ処理を行う時に、意味合いがまとまった情報を1個のデータ集合として考えます。例えば、「個人情報」「会社情報」「商品情報」「売上データ」「売上統計データ」などが在ると思います。

それぞれを別のテーブルとして情報を保存しておきますが、このなかで「個人情報」「会社情報」「商品情報」など1回データを作成すればあまり変化がない情報をマスタデータとしてそれぞれ別々のテーブルとして持つことが一般的です。（あまり変化がないと言いましたが、それぞれの情報は更新できる必要があります。この作業をマスタメンテナンスと言います。）

ここでは、「商品情報」と「売上データ」に関してのみを例にして説明したいと思います。
「商品情報」（商品マスタと言い換えます）は以下の様な項目が必要だと思います。

商品コード（商品の型番？）
商品名
単価

売上NO
商品コード
日付
数量
単価（単価の変更があるため）
金額

-- 商品マスタ
CREATE OR REPLACE TABLE tm_prod (
	id    INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    code  VARCHAR(32),	-- 商品コード
    name  VARCHAR(32),	-- 商品名
    price INT,          -- 単価
	del	  INT DEFAULT 0 -- 削除フラグ:0(存在),1:(削除)
);

-- 売上データ
CREATE OR REPLACE TABLE tt_sales (
    no        INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 売上NO
    id_prd    INT,      -- 商品マスタ.id
    salesdate DATE,	    -- 日付
    volume    INT,      -- 数量
    price     INT,      -- 単価
    amount    INT,      -- 金額
	del	  INT DEFAULT 0 -- 削除フラグ:0(存在),1:(削除)
);

それでは、上記のテーブルに以下の SQL でテストデータを登録します。

-- 商品テーブル
INSERT INTO tm_prod(code, name, price) VALUES
    ('pen01', '鉛筆HB', 80), 
    ('pen02', '鉛筆B' , 100),
    ('pen03', '鉛筆B2' , 100),
    ('cre01', 'くれよん12色', 1000),
    ('cre02', 'くれよん24色', 2000),
    ('pen10', '蛍光ペン', 120),
    ('pen11', '蛍光ペン2', 130),
    ('era01', '消しゴム', 150),
    ('era02', '消しゴム2', 200);
-- 売上件数テーブル
INSERT INTO tt_sales(id_prd, salesdate, volume, price, amount) VALUES
    (1, '2024-01-04', 10,   80,  800), 
    (4, '2024-01-04',  1, 1000, 1000), 
    (6, '2024-01-04',  2,  120,  240), 
    (7, '2024-01-05',  3,  130,  390), 
    (1, '2024-01-07', 15,   80, 1200), 
    (2, '2024-01-07', 10,  100, 1000), 
    (5, '2024-01-07',  2, 2000, 4000), 
    (1, '2024-01-05', 20,   80, 1600), 
    (3, '2024-01-05',  1,  100,  100), 
    (4, '2024-01-10',  2, 1000, 2000), 
    (2, '2024-01-15',  2,  100,  200), 
    (6, '2024-01-05',  5,  120,  600), 
    (7, '2024-01-14',  3,  130,  390), 
    (8, '2024-01-20',  1,  150,  150), 
    (5, '2024-01-20',  4, 2000, 8000), 
    (8, '2024-01-22', 10,  150, 1500), 
    (9, '2024-01-22', 10,  200, 2000);

テストデータを確認します。

MariaDB [test]> select * from tm_prod;
+----+-------+--------------+-------+------+
| id | code  | name         | price | del  |
+----+-------+--------------+-------+------+
| 19 | pen01 | 鉛筆HB       |    80 |    0 |
| 20 | pen02 | 鉛筆B        |   100 |    0 |
| 21 | pen03 | 鉛筆B2       |   100 |    0 |
| 22 | cre01 | くれよん12色 |  1000 |    0 |
| 23 | cre02 | くれよん24色 |  2000 |    0 |
| 24 | pen10 | 蛍光ペン     |   120 |    0 |
| 25 | pen11 | 蛍光ペン2    |   130 |    0 |
| 26 | era01 | 消しゴム     |   150 |    0 |
| 27 | era02 | 消しゴム2    |   200 |    0 |
+----+-------+--------------+-------+------+
9 rows in set (0.00 sec)

MariaDB [test]> select * from tt_sales;
+----+--------+------------+--------+-------+--------+------+
| no | id_prd | salesdate  | volume | price | amount | del  |
+----+--------+------------+--------+-------+--------+------+
|  1 |      1 | 2024-01-04 |     10 |    80 |    800 |    0 |
|  2 |      4 | 2024-01-04 |      1 |  1000 |   1000 |    0 |
|  3 |      6 | 2024-01-04 |      2 |   120 |    240 |    0 |
|  4 |      7 | 2024-01-05 |      3 |   130 |    390 |    0 |
|  5 |      1 | 2024-01-07 |     15 |    80 |   1200 |    0 |
|  6 |      2 | 2024-01-07 |     10 |   100 |   1000 |    0 |
|  7 |      5 | 2024-01-07 |      2 |  2000 |   4000 |    0 |
|  8 |      1 | 2024-01-05 |     20 |    80 |   1600 |    0 |
|  9 |      3 | 2024-01-05 |      1 |   100 |    100 |    0 |
| 10 |      4 | 2024-01-10 |      2 |  1000 |   2000 |    0 |
| 11 |      2 | 2024-01-15 |      2 |   100 |    200 |    0 |
| 12 |      6 | 2024-01-05 |      5 |   120 |    600 |    0 |
| 13 |      7 | 2024-01-14 |      3 |   130 |    390 |    0 |
| 14 |      8 | 2024-01-20 |      1 |   150 |    150 |    0 |
| 15 |      5 | 2024-01-20 |      4 |  2000 |   8000 |    0 |
| 16 |      8 | 2024-01-22 |     10 |   150 |   1500 |    0 |
| 17 |      9 | 2024-01-22 |     10 |   200 |   2000 |    0 |
+----+--------+------------+--------+-------+--------+------+
17 rows in set (0.00 sec)

■売上データの一覧リストの取得について

それでは商品マスタから「名称」「商品コード」を取得する様にした、売上データの一覧をリストする SQL を示します。
「売上データ」の「商品id」と「商品マスタ」の「商品id」を INNER JOIN にて結合しています。
さらに、結合で「商品マスタ」の「削除フラグ」が「0:未削除」の条件付けを行っています。

SELECT 
    tt_sales.no AS '売上no',
    tt_sales.id_prd AS '商品id',
    tm_prod.code AS '商品コード',
    tm_prod.name AS '商品名',
    tt_sales.volume AS '数量',
    tt_sales.price AS '単価',
    tt_sales.amount AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
ORDER BY tt_sales.no;

この SQL の結果は以下の通りです。

+--------+--------+------------+--------------+------+------+------+
| 売上no | 商品id | 商品コード | 商品名       | 数量 | 単価 | 金額 |
+--------+--------+------------+--------------+------+------+------+
|      1 |      1 | pen01      | 鉛筆HB       |   10 |   80 |  800 |
|      2 |      4 | cre01      | くれよん12色 |    1 | 1000 | 1000 |
|      3 |      6 | pen10      | 蛍光ペン     |    2 |  120 |  240 |
|      4 |      7 | pen11      | 蛍光ペン2    |    3 |  130 |  390 |
|      5 |      1 | pen01      | 鉛筆HB       |   15 |   80 | 1200 |
|      6 |      2 | pen02      | 鉛筆B        |   10 |  100 | 1000 |
|      7 |      5 | cre02      | くれよん24色 |    2 | 2000 | 4000 |
|      8 |      1 | pen01      | 鉛筆HB       |   20 |   80 | 1600 |
|      9 |      3 | pen03      | 鉛筆B2       |    1 |  100 |  100 |
|     10 |      4 | cre01      | くれよん12色 |    2 | 1000 | 2000 |
|     11 |      2 | pen02      | 鉛筆B        |    2 |  100 |  200 |
|     12 |      6 | pen10      | 蛍光ペン     |    5 |  120 |  600 |
|     13 |      7 | pen11      | 蛍光ペン2    |    3 |  130 |  390 |
|     14 |      8 | era01      | 消しゴム     |    1 |  150 |  150 |
|     15 |      5 | cre02      | くれよん24色 |    4 | 2000 | 8000 |
|     16 |      8 | era01      | 消しゴム     |   10 |  150 | 1500 |
|     17 |      9 | era02      | 消しゴム2    |   10 |  200 | 2000 |
+--------+--------+------------+--------------+------+------+------+
17 rows in set (0.00 sec)

「商品マスタ」との結合ですが、「削除フラグ:0」のデータのみを取得する SELECT文をサブクエリのようにすれば、以下の SQL でも同様のことができます。

SELECT 
    tt_sales.no AS '売上no',
    tt_sales.id_prd AS '商品id',
    TX.code AS '商品コード',
    TX.name AS '商品名',
    tt_sales.volume AS '数量',
    tt_sales.price AS '単価',
    tt_sales.amount AS '金額'
FROM tt_sales
    INNER JOIN (
        SELECT * 
        FROM tm_prod
        WHERE tm_prod.del = 0
    ) TX
    ON  TX.id = tt_sales.id_prd
ORDER BY tt_sales.no;

■商品マスタが削除済みの場合の売上データの一覧リストの取得について

削除済みの商品マスタを設定し、売上データの一覧リストでどの様に処理するのか説明します。

最初に、商品マスタ内に削除済みデータを更新するため以下の SQL を実行します。

UPDATE tm_prod SET del = 1
WHERE id IN(4, 7);

「id」が「4,7」のデータの削除フラグが「1」になることを確認します。

MariaDB [test]> select * from tm_prod;
+----+-------+--------------+-------+------+
| id | code  | name         | price | del  |
+----+-------+--------------+-------+------+
|  1 | pen01 | 鉛筆HB       |    80 |    0 |
|  2 | pen02 | 鉛筆B        |   100 |    0 |
|  3 | pen03 | 鉛筆B2       |   100 |    0 |
|  4 | cre01 | くれよん12色 |  1000 |    1 |
|  5 | cre02 | くれよん24色 |  2000 |    0 |
|  6 | pen10 | 蛍光ペン     |   120 |    0 |
|  7 | pen11 | 蛍光ペン2    |   130 |    1 |
|  8 | era01 | 消しゴム     |   150 |    0 |
|  9 | era02 | 消しゴム2    |   200 |    0 |
+----+-------+--------------+-------+------+
9 rows in set (0.00 sec)

この状況で、先ほどのどちらかの SQL で売上一覧を実行してみます。

MariaDB [test]> SELECT
    ->     tt_sales.no AS '売上no',
    ->     tt_sales.id_prd AS '商品id',
    ->     TX.code AS '商品コード',
    ->     TX.name AS '商品名',
    ->     tt_sales.volume AS '数量',
    ->     tt_sales.price AS '単価',
    ->     tt_sales.amount AS '金額'
    -> FROM tt_sales
    ->     INNER JOIN (
    ->         SELECT *
    ->         FROM tm_prod
    ->         WHERE tm_prod.del = 0
    ->     ) TX
    ->     ON  TX.id = tt_sales.id_prd
    -> ORDER BY tt_sales.no;
+--------+--------+------------+--------------+------+------+------+
| 売上no | 商品id | 商品コード | 商品名       | 数量 | 単価 | 金額 |
+--------+--------+------------+--------------+------+------+------+
|      1 |      1 | pen01      | 鉛筆HB       |   10 |   80 |  800 |
|      3 |      6 | pen10      | 蛍光ペン     |    2 |  120 |  240 |
|      5 |      1 | pen01      | 鉛筆HB       |   15 |   80 | 1200 |
|      6 |      2 | pen02      | 鉛筆B        |   10 |  100 | 1000 |
|      7 |      5 | cre02      | くれよん24色 |    2 | 2000 | 4000 |
|      8 |      1 | pen01      | 鉛筆HB       |   20 |   80 | 1600 |
|      9 |      3 | pen03      | 鉛筆B2       |    1 |  100 |  100 |
|     11 |      2 | pen02      | 鉛筆B        |    2 |  100 |  200 |
|     12 |      6 | pen10      | 蛍光ペン     |    5 |  120 |  600 |
|     14 |      8 | era01      | 消しゴム     |    1 |  150 |  150 |
|     15 |      5 | cre02      | くれよん24色 |    4 | 2000 | 8000 |
|     16 |      8 | era01      | 消しゴム     |   10 |  150 | 1500 |
|     17 |      9 | era02      | 消しゴム2    |   10 |  200 | 2000 |
+--------+--------+------------+--------------+------+------+------+
13 rows in set (0.00 sec)

確かに、商品マスタの「削除フラグ:1」のデータは一覧に表示されません。
一覧としてはこれで正解のですが、削除済みのものも見たいという要求があれば INNER JOIN を LEFT JOIN に変更し「売上データ」側には「商品id」が存在するが「商品マスタ」側にはデータが削除されているものも表示する様にします。

MariaDB [test]> SELECT
    ->     tt_sales.no AS '売上no',
    ->     tt_sales.id_prd AS '商品id',
    ->     TX.code AS '商品コード',
    ->     TX.name AS '商品名',
    ->     tt_sales.volume AS '数量',
    ->     tt_sales.price AS '単価',
    ->     tt_sales.amount AS '金額'
    -> FROM tt_sales
    ->     LEFT JOIN (
    ->         SELECT *
    ->         FROM tm_prod
    ->         WHERE tm_prod.del = 0
    ->     ) TX
    ->     ON  TX.id = tt_sales.id_prd
    -> ORDER BY tt_sales.no;
+--------+--------+------------+--------------+------+------+------+
| 売上no | 商品id | 商品コード | 商品名       | 数量 | 単価 | 金額 |
+--------+--------+------------+--------------+------+------+------+
|      1 |      1 | pen01      | 鉛筆HB       |   10 |   80 |  800 |
|      2 |      4 | NULL       | NULL         |    1 | 1000 | 1000 |
|      3 |      6 | pen10      | 蛍光ペン     |    2 |  120 |  240 |
|      4 |      7 | NULL       | NULL         |    3 |  130 |  390 |
|      5 |      1 | pen01      | 鉛筆HB       |   15 |   80 | 1200 |
|      6 |      2 | pen02      | 鉛筆B        |   10 |  100 | 1000 |
|      7 |      5 | cre02      | くれよん24色 |    2 | 2000 | 4000 |
|      8 |      1 | pen01      | 鉛筆HB       |   20 |   80 | 1600 |
|      9 |      3 | pen03      | 鉛筆B2       |    1 |  100 |  100 |
|     10 |      4 | NULL       | NULL         |    2 | 1000 | 2000 |
|     11 |      2 | pen02      | 鉛筆B        |    2 |  100 |  200 |
|     12 |      6 | pen10      | 蛍光ペン     |    5 |  120 |  600 |
|     13 |      7 | NULL       | NULL         |    3 |  130 |  390 |
|     14 |      8 | era01      | 消しゴム     |    1 |  150 |  150 |
|     15 |      5 | cre02      | くれよん24色 |    4 | 2000 | 8000 |
|     16 |      8 | era01      | 消しゴム     |   10 |  150 | 1500 |
|     17 |      9 | era02      | 消しゴム2    |   10 |  200 | 2000 |
+--------+--------+------------+--------------+------+------+------+
17 rows in set (0.00 sec)

削除された「商品マスタ」の商品コード、商品名は NULL で表示されます。
NULL が気に入らないのであれば、以下の様にカラム表示の中を変えてやります。

MariaDB [test]> SELECT
    ->     tt_sales.no AS '売上no',
    ->     tt_sales.id_prd AS '商品id',
    ->     IFNULL(TX.code, '-----') AS '商品コード',
    ->     IFNULL(TX.name, '*削除済*') AS '商品名',
    ->     tt_sales.volume AS '数量',
    ->     tt_sales.price AS '単価',
    ->     tt_sales.amount AS '金額'
    -> FROM tt_sales
    ->     LEFT JOIN (
    ->         SELECT *
    ->         FROM tm_prod
    ->         WHERE tm_prod.del = 0
    ->     ) TX
    ->     ON  TX.id = tt_sales.id_prd
    -> ORDER BY tt_sales.no;
+--------+--------+------------+--------------+------+------+------+
| 売上no | 商品id | 商品コード | 商品名       | 数量 | 単価 | 金額 |
+--------+--------+------------+--------------+------+------+------+
|      1 |      1 | pen01      | 鉛筆HB       |   10 |   80 |  800 |
|      2 |      4 | -----      | *削除済*     |    1 | 1000 | 1000 |
|      3 |      6 | pen10      | 蛍光ペン     |    2 |  120 |  240 |
|      4 |      7 | -----      | *削除済*     |    3 |  130 |  390 |
|      5 |      1 | pen01      | 鉛筆HB       |   15 |   80 | 1200 |
|      6 |      2 | pen02      | 鉛筆B        |   10 |  100 | 1000 |
|      7 |      5 | cre02      | くれよん24色 |    2 | 2000 | 4000 |
|      8 |      1 | pen01      | 鉛筆HB       |   20 |   80 | 1600 |
|      9 |      3 | pen03      | 鉛筆B2       |    1 |  100 |  100 |
|     10 |      4 | -----      | *削除済*     |    2 | 1000 | 2000 |
|     11 |      2 | pen02      | 鉛筆B        |    2 |  100 |  200 |
|     12 |      6 | pen10      | 蛍光ペン     |    5 |  120 |  600 |
|     13 |      7 | -----      | *削除済*     |    3 |  130 |  390 |
|     14 |      8 | era01      | 消しゴム     |    1 |  150 |  150 |
|     15 |      5 | cre02      | くれよん24色 |    4 | 2000 | 8000 |
|     16 |      8 | era01      | 消しゴム     |   10 |  150 | 1500 |
|     17 |      9 | era02      | 消しゴム2    |   10 |  200 | 2000 |
+--------+--------+------------+--------------+------+------+------+
17 rows in set (0.00 sec)

MySQL
0CM

2024/01/26

MySQL ビューを作成と使用方法について(CREATE VIEW)

データの編集などを含んだ SELECT文 を別のテーブルの様に扱えるのが ビュー(VIEW) で毎回同じ SELECT文 を記述しなくても通常のテーブルの様に扱えます。

この ビュー(VIEW) を作成を CREATE VIEW で行います。

CREATE VIEW について順を追って説明します。

CREATE VIEW の書き方
CREATE VIEW の元となるテーブルについて
CREATE VIEW とその利用

■CREATE VIEWの書き方

CREATE VIEW文 の書式は以下の通りです。

CREATE [OR REPLACE] VIEW view_name [(column_list)] AS select_statement
-- view_name        : ビューの名称
-- [(column_list)]  : カラム名リスト（select_statement のカラムリストに名前を付ける）
-- select_statement : ビューの元となる SELECT文

なお CREATE OR REPLACE VIEW の指定の場合は、既にビューが存在している場合は上書きを行います。

それでは簡単例のビューの作成を行ってみます。ビューの元となる SELECT文はリテラルの SELECT でも問題ないので、UNION の説明で使用した以下の SQL を当てます。

⇒ MySQL 異なるデータ取得（SELECT）結果を結合して取得する方法について（UNION）

CREATE VIEW t_view AS 
    SELECT '1' AS 'COL1', '2' AS 'COL2'
    UNION
    SELECT 'A' AS 'COLA', 'B' AS 'COLB';

作成されたビューの一覧を表示します。

MariaDB [test]> SELECT * FROM t_view;
+------+------+
| COL1 | COL2 |
+------+------+
| 1    | 2    |
| A    | B    |
+------+------+
2 rows in set (0.00 sec)

それではビューのカラム名を変更してみます。ビューの上書きなので以下の文となります。

CREATE OR REPLACE VIEW t_view(COLUMN1, COLUMN2) AS 
    SELECT '1' AS 'COL1', '2' AS 'COL2'
    UNION
    SELECT 'A' AS 'COLA', 'B' AS 'COLB';

変更されたビューの一覧を表示します。カラム名称が変更されたことが分かります。

MariaDB [test]> SELECT * FROM t_view;
+---------+---------+
| COLUMN1 | COLUMN2 |
+---------+---------+
| 1       | 2       |
| A       | B       |
+---------+---------+
2 rows in set (0.00 sec)

この様なリテラル表では面白くないので、テーブルを使った例を以降に記します。

■CREATE VIEW の元となるテーブルについて

以下のページで使用した 商品テーブル（マスタ） と 売上数量テーブル を使用してビューにします。
⇒ MySQL データ取得（SELECT）のWHRE句にサブクエリ条件で ANY ALL の使い方について（ANY、SOME、ALL）
商品テーブル（マスタ） と 売上数量テーブル は以下の通りです。

MariaDB [test]> SELECT * FROM product;
+----+----------+-------+
| id | name     | price |
+----+----------+-------+
|  1 | 鉛筆A    |    80 |
|  2 | 鉛筆B    |   100 |
|  3 | くれよん |   500 |
|  4 | 蛍光ペン |   120 |
|  5 | 消しゴム |   150 |
+----+----------+-------+
5 rows in set (0.00 sec)

MariaDB [test]> SELECT * FROM sales;
+----+--------+------------+--------+
| id | id_prd | salesdate  | volume |
+----+--------+------------+--------+
|  1 |      1 | 2024-01-04 |     10 |
|  2 |      1 | 2024-01-07 |     15 |
|  3 |      2 | 2024-01-07 |     20 |
|  4 |      3 | 2024-01-05 |      1 |
|  5 |      3 | 2024-01-10 |      2 |
|  6 |      3 | 2024-01-15 |      2 |
|  7 |      4 | 2024-01-05 |      5 |
|  8 |      4 | 2024-01-14 |      3 |
|  9 |      4 | 2024-01-20 |      1 |
| 10 |      5 | 2024-01-20 |      5 |
| 11 |      5 | 2024-01-22 |     10 |
+----+--------+------------+--------+
11 rows in set (0.01 sec)

商品テーブルと売上数量テーブルを連結しての商品名称入りの一覧を表示してみます。
今回のテストのための基本の SELECT文となります。

SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
LEFT JOIN product
ON sales.id_prd = product.id
ORDER BY sales.id;

この SQL の結果は以下の通りです。

+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  1 |      1 | 鉛筆A    | 2024-01-04 |     10 |
|  2 |      1 | 鉛筆A    | 2024-01-07 |     15 |
|  3 |      2 | 鉛筆B    | 2024-01-07 |     20 |
|  4 |      3 | くれよん | 2024-01-05 |      1 |
|  5 |      3 | くれよん | 2024-01-10 |      2 |
|  6 |      3 | くれよん | 2024-01-15 |      2 |
|  7 |      4 | 蛍光ペン | 2024-01-05 |      5 |
|  8 |      4 | 蛍光ペン | 2024-01-14 |      3 |
|  9 |      4 | 蛍光ペン | 2024-01-20 |      1 |
| 10 |      5 | 消しゴム | 2024-01-20 |      5 |
| 11 |      5 | 消しゴム | 2024-01-22 |     10 |
+----+--------+----------+------------+--------+
11 rows in set (0.00 sec)

■CREATE VIEW とその利用

上記の商品名称入りの一覧表示の SELECT文を使ってビューを作成します。

CREATE VIEW v_sales(id_sal, id_prd, prd_name, salesdate, salesvol) AS 
  SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
  LEFT JOIN product
  ON sales.id_prd = product.id;

作成されたビューを表示します。

MariaDB [test]> SELECT * FROM v_sales;
+--------+--------+----------+------------+----------+
| id_sal | id_prd | prd_name | salesdate  | salesvol |
+--------+--------+----------+------------+----------+
|      1 |      1 | 鉛筆A    | 2024-01-04 |       10 |
|      2 |      1 | 鉛筆A    | 2024-01-07 |       15 |
|      3 |      2 | 鉛筆B    | 2024-01-07 |       20 |
|      4 |      3 | くれよん | 2024-01-05 |        1 |
|      5 |      3 | くれよん | 2024-01-10 |        2 |
|      6 |      3 | くれよん | 2024-01-15 |        2 |
|      7 |      4 | 蛍光ペン | 2024-01-05 |        5 |
|      8 |      4 | 蛍光ペン | 2024-01-14 |        3 |
|      9 |      4 | 蛍光ペン | 2024-01-20 |        1 |
|     10 |      5 | 消しゴム | 2024-01-20 |        5 |
|     11 |      5 | 消しゴム | 2024-01-22 |       10 |
+--------+--------+----------+------------+----------+
11 rows in set (0.11 sec)

カラム名がビュー作成で指定した名称になっていることが分かります。

なお、ビューは通常のテーブルの様に扱うことができます。
例えば WHERE で条件を付けたり、ORDER BY と LIMIT で表示を制限したりできます。

MariaDB [test]> SELECT * FROM v_sales
    -> WHERE salesdate >= '2024-01-05'
    -> ORDER BY salesvol
    -> LIMIT 8;
+--------+--------+----------+------------+----------+
| id_sal | id_prd | prd_name | salesdate  | salesvol |
+--------+--------+----------+------------+----------+
|      4 |      3 | くれよん | 2024-01-05 |        1 |
|      9 |      4 | 蛍光ペン | 2024-01-20 |        1 |
|      5 |      3 | くれよん | 2024-01-10 |        2 |
|      6 |      3 | くれよん | 2024-01-15 |        2 |
|      8 |      4 | 蛍光ペン | 2024-01-14 |        3 |
|      7 |      4 | 蛍光ペン | 2024-01-05 |        5 |
|     10 |      5 | 消しゴム | 2024-01-20 |        5 |
|     11 |      5 | 消しゴム | 2024-01-22 |       10 |
+--------+--------+----------+------------+----------+
8 rows in set (0.03 sec)

MySQL
0CM

2024/01/26

MySQL 異なるデータ取得（SELECT）結果を結合して取得する方法について（UNION）

似た様な複数の SELECT文 の結果を結合するために、連結するのが UNION です。
各 SELECT文 のカラム数及びデータ型は一致させておくべきです。

UNION について順を追って説明します。

UNIONの書き方
UNION DISTINCT と UNION ALL
UNION での ORDER BY と LIMIT
UNION での ORDER BY と LIMIT その例1
UNION での ORDER BY と LIMIT その例2

■UNIONの書き方

複数の SELECT文 の結果を結合する UNION の書式は以下の様です。

SELECT 「カラム一覧１」FROM 「テーブル１」...
UNION [ALL | DISTINCT] SELECT 「カラム一覧２」FROM 「テーブル２」...
[UNION [ALL | DISTINCT] SELECT 「カラム一覧Ｎ」FROM 「テーブルＮ」...]
-- UNION のみ記述 : 重複行は削除されます。(UNION DISTINCT)と同様
-- UNION ALL      : 重複行もそのまま表示されます。

UNION 結果セットのカラム名は、最初の SELECT ステートメントのカラム名が採用されます。
各 SELECT ステートメントの対応する位置にリストされているカラムは、同じデータ型に揃えた方が良いようです。

以下にテーブルをリテラルで書いた SQL を示します。

MariaDB [test]> SELECT '1' AS 'COL1', '2' AS 'COL2';
+------+------+
| COL1 | COL2 |
+------+------+
| 1    | 2    |
+------+------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT 'A' AS 'COLA', 'B' AS 'COLB';
+------+------+
| COLA | COLB |
+------+------+
| A    | B    |
+------+------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT '1' AS 'COL1', '2' AS 'COL2'
    -> UNION
    -> SELECT 'A' AS 'COLA', 'B' AS 'COLB';
+------+------+
| COL1 | COL2 |
+------+------+
| 1    | 2    |
| A    | B    |
+------+------+
2 rows in set (0.00 sec)

1番目、2番目の SQL はテーブルを個別にデータ取得し、3番目の SQL で UNION を行っています。
確かに結合された結果セットが返されます。

■UNION DISTINCT と UNION ALL

結合の方法で、重複する行を削除する場合と、全てを取得する場合の例を示します。

以下の SQL は4個の SELECT文を UNION で連結しています。
3，4番目の SELECT文は、1，2番目の SELECT文と同じデータを返しますので重複分となり、結果セットには 1，2番目の SELECT文の結果のみが返される様になります。

MariaDB [test]> SELECT '1' AS 'COL1', '2' AS 'COL2'
    -> UNION
    -> SELECT 'A' AS 'COLA', 'B' AS 'COLB'
    -> UNION
    -> SELECT '1' AS 'COL11', '2' AS 'COL22'
    -> UNION
    -> SELECT 'A' AS 'COLAA', 'B' AS 'COLBB';
+------+------+
| COL1 | COL2 |
+------+------+
| 1    | 2    |
| A    | B    |
+------+------+
2 rows in set (0.00 sec)

以下の SQL は4個の SELECT文を UNION ALL で連結しています。
結果セットとしては　1，2，3，4番目の SELECT文のデータを順に返しています。

MariaDB [test]> SELECT '1' AS 'COL1', '2' AS 'COL2'
    -> UNION ALL
    -> SELECT 'A' AS 'COLA', 'B' AS 'COLB'
    -> UNION ALL
    -> SELECT '1' AS 'COL11', '2' AS 'COL22'
    -> UNION ALL
    -> SELECT 'A' AS 'COLAA', 'B' AS 'COLBB';
+------+------+
| COL1 | COL2 |
+------+------+
| 1    | 2    |
| A    | B    |
| 1    | 2    |
| A    | B    |
+------+------+
4 rows in set (0.00 sec)

■UNION での ORDER BY と LIMIT

UNION で連結される SELECT文 の中で ORDER BY と LIMIT を使う場合は SELECT文 をカッコ「()」で囲みその中に配置します。以下の様な感じです。

(SELECT 「カラムリスト1」 FROM table1 WHERE 「条件1」 ORDER BY 「ソート指定1」 LIMIT 「行数1」)
UNION
(SELECT 「カラムリスト2」 FROM table2 WHERE 「条件2」 ORDER BY 「ソート指定2」 LIMIT 「行数2」);

ORDER BY と LIMIT を使う例を示すために、以下のページで作成したテーブルを使用します。

⇒ MySQL データ取得（SELECT）のWHRE句にサブクエリ条件で ANY ALL の使い方について（ANY、SOME、ALL）
以下の様な 商品テーブル（マスタ） と 売上数量テーブル を使用します。

MariaDB [test]> SELECT * FROM product;
+----+----------+-------+
| id | name     | price |
+----+----------+-------+
|  1 | 鉛筆A    |    80 |
|  2 | 鉛筆B    |   100 |
|  3 | くれよん |   500 |
|  4 | 蛍光ペン |   120 |
|  5 | 消しゴム |   150 |
+----+----------+-------+
5 rows in set (0.00 sec)

MariaDB [test]> SELECT * FROM sales;
+----+--------+------------+--------+
| id | id_prd | salesdate  | volume |
+----+--------+------------+--------+
|  1 |      1 | 2024-01-04 |     10 |
|  2 |      1 | 2024-01-07 |     15 |
|  3 |      2 | 2024-01-07 |     20 |
|  4 |      3 | 2024-01-05 |      1 |
|  5 |      3 | 2024-01-10 |      2 |
|  6 |      3 | 2024-01-15 |      2 |
|  7 |      4 | 2024-01-05 |      5 |
|  8 |      4 | 2024-01-14 |      3 |
|  9 |      4 | 2024-01-20 |      1 |
| 10 |      5 | 2024-01-20 |      5 |
| 11 |      5 | 2024-01-22 |     10 |
+----+--------+------------+--------+
11 rows in set (0.01 sec)

商品テーブルと売上数量テーブルを連結しての商品名称入りの一覧を表示してみます。
今回のテストのための基本の SQL となります。

SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
LEFT JOIN product
ON sales.id_prd = product.id
ORDER BY sales.id;

この SQL の結果は以下の通りです。

+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  1 |      1 | 鉛筆A    | 2024-01-04 |     10 |
|  2 |      1 | 鉛筆A    | 2024-01-07 |     15 |
|  3 |      2 | 鉛筆B    | 2024-01-07 |     20 |
|  4 |      3 | くれよん | 2024-01-05 |      1 |
|  5 |      3 | くれよん | 2024-01-10 |      2 |
|  6 |      3 | くれよん | 2024-01-15 |      2 |
|  7 |      4 | 蛍光ペン | 2024-01-05 |      5 |
|  8 |      4 | 蛍光ペン | 2024-01-14 |      3 |
|  9 |      4 | 蛍光ペン | 2024-01-20 |      1 |
| 10 |      5 | 消しゴム | 2024-01-20 |      5 |
| 11 |      5 | 消しゴム | 2024-01-22 |     10 |
+----+--------+----------+------------+--------+
11 rows in set (0.00 sec)

■UNION での ORDER BY と LIMIT その例1

以下に連結される SELECT文の中に ORDER BY と LIMIT を持つテスト SQL を実行します。

最初の SELECT文は、上記の売上数量テーブルの売上日付に対して '2024-01-05' 以前の条件を付けて、出力件数を「2」にし、後の SELECT文は、売上数量テーブルの売上日付に対して '2024-01-20' 以降の条件を付けて、出力件数を「2」に設定しています。

MariaDB [test]> (
    ->     SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    ->     LEFT JOIN product
    ->     ON sales.id_prd = product.id
    ->     WHERE sales.salesdate <= '2024-01-05'
    ->     ORDER BY sales.id
    ->     LIMIT 2
    -> )
    -> UNION
    -> (
    ->     SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    ->     LEFT JOIN product
    ->     ON sales.id_prd = product.id
    ->     WHERE sales.salesdate >= '2024-01-20'
    ->     ORDER BY sales.id
    ->     LIMIT 2
    -> );
+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  1 |      1 | 鉛筆A    | 2024-01-04 |     10 |
|  4 |      3 | くれよん | 2024-01-05 |      1 |
|  9 |      4 | 蛍光ペン | 2024-01-20 |      1 |
| 10 |      5 | 消しゴム | 2024-01-20 |      5 |
+----+--------+----------+------------+--------+
4 rows in set (0.00 sec)

最終的な結果セットは「4」行ですが、2個の SELECT文を個別に LIMIT 無しで実行してみます。

MariaDB [test]> SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    -> LEFT JOIN product
    -> ON sales.id_prd = product.id
    -> WHERE sales.salesdate <= '2024-01-05'
    -> ORDER BY sales.id;
+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  1 |      1 | 鉛筆A    | 2024-01-04 |     10 |
|  4 |      3 | くれよん | 2024-01-05 |      1 |
|  7 |      4 | 蛍光ペン | 2024-01-05 |      5 |
+----+--------+----------+------------+--------+
3 rows in set (0.00 sec)

MariaDB [test]> SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    -> LEFT JOIN product
    -> ON sales.id_prd = product.id
    -> WHERE sales.salesdate >= '2024-01-20'
    -> ORDER BY sales.id;
+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  9 |      4 | 蛍光ペン | 2024-01-20 |      1 |
| 10 |      5 | 消しゴム | 2024-01-20 |      5 |
| 11 |      5 | 消しゴム | 2024-01-22 |     10 |
+----+--------+----------+------------+--------+
3 rows in set (0.00 sec)

この結果から、UNION で連結した SQL では確かに個別の LIMIT が効いていることが分かります。

■UNION での ORDER BY と LIMIT その例2

UNION で連結された結果セットに対して ORDER BY と LIMIT を行う場合は、最後の文の後ろに ORDER BY と LIMIT を配置します。

上記の UNION の例の SQL で個別の SELECT文では ORDER BY と LIMIT を行わず、最後に配置してみます。

MariaDB [test]> (
    ->     SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    ->     LEFT JOIN product
    ->     ON sales.id_prd = product.id
    ->     WHERE sales.salesdate <= '2024-01-05'
    -> )
    -> UNION
    -> (
    ->     SELECT sales.id, sales.id_prd, product.name, sales.salesdate, sales.volume FROM sales
    ->     LEFT JOIN product
    ->     ON sales.id_prd = product.id
    ->     WHERE sales.salesdate >= '2024-01-20'
    -> )
    -> ORDER BY volume
    -> LIMIT 5;
+----+--------+----------+------------+--------+
| id | id_prd | name     | salesdate  | volume |
+----+--------+----------+------------+--------+
|  9 |      4 | 蛍光ペン | 2024-01-20 |      1 |
|  4 |      3 | くれよん | 2024-01-05 |      1 |
| 10 |      5 | 消しゴム | 2024-01-20 |      5 |
|  7 |      4 | 蛍光ペン | 2024-01-05 |      5 |
|  1 |      1 | 鉛筆A    | 2024-01-04 |     10 |
+----+--------+----------+------------+--------+
5 rows in set (0.05 sec)

売上数量の昇順で全ての UNION の結果データをソートしてから、先頭から5行の結果セットになっています。

MySQL
0CM