[2024/02/07] MySQL データのグループ化(集計処理)の条件付けについて(GROUP BY句、HAVING句) (No.355)
[2024/02/06] MySQL データのグループ化(集計処理)について(GROUP BY句、WITH ROLLUP) (No.354)
[2024/02/02] MySQL SELECT文の連結テーブルの有り無しの指定ついて(SELECT WHERE EXISTS) (No.353)
[2024/01/29] MySQL マスタテーブルの作成と参照に関する考察について(CREATE TABLE、AUTOINCREMENT、JOIN、LEFT JOIN) (No.352)
[2024/01/26] MySQL ビューを作成と使用方法について(CREATE VIEW) (No.351)
-
×
[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。
-
データのグループ化(集計処理)を行う時には GROUP BY句 を使うことは、以下の記事で説明しました。
⇒MySQL データのグループ化(集計処理)について(GROUP BY句、WITH ROLLUP)
今回は、グループ化された結果データに対して条件付けの方法について説明します。 この条件を付けのために HAVING句 を使いますがその例と、さらに WHERE句 と同時に使った時の注意点について記します。
以下の内容で、順を追って説明します。■HAVING句の書き方ついて
HAVING句の書き方は以下の様な構文で指定します。
SELECT カラム名1 [, カラム名2, ...] ,[集計関数...] FROM テーブル名... GROUP BY カラム名1 [, カラム名2, ...] HAVING 条件式
GROUP BY の後ろに指定されたカラム名の値が同じデータのグループ化を行います。 複数のカラム名が指定された場合は、それぞれのカラム名の値が同じものでグループ化を行います。
HAVING句は は GROUP BY の後ろに「HAVING」に続いて条件式を記述します。 条件式はグループ化で使用したカラムに対しする条件や、集計関数を使った条件式となります。
テストデータとして「商品情報」と「売上データ」のテーブルを使い、テーブルの連結を行いながら、 グループ化について説明したいと思います。
これについては、最初に触れましたが、以下の記事のテーブルを使用します。
⇒MySQL データのグループ化(集計処理)について(GROUP BY句、WITH ROLLUP)
■HAVING句にグループ化のカラム名を使う例について
先ずは HAVING句 を使わずに売上データに対してグループ化を行う為に GROUP BY に1個のカラムとして「商品分類」を指定し、 「商品分類」ごとの商品の売上数と売上金額の合計数を表示する SQL を示します。
「商品分類」は「商品マスタ」に持っているため、「売上データ」と「商品マスタ」を FROM句 で INNER JOIN で結合します。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)
それでは「商品分類」が「pen」の文字列を含むデータを抽出するため HAVING句 を以下の様に追加します。
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 HAVING tm_prod.class LIKE '%pen%' ORDER BY tm_prod.class;
この SQL の結果は以下の通りです。
+----------+------+------+ | 商品分類 | 数量 | 金額 | +----------+------+------+ | kpen | 13 | 1620 | | pen | 58 | 4900 | +----------+------+------+ 2 rows in set (0.01 sec)
確かに「商品分類」に「pen」の文字列を含むデータのみが表示されました。
■HAVING句に集計関数を使う例について
今度は上記の集計 SQL に「金額」が4000円以上のデータを抽出するため HAVING句 を以下の様に追加します。
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 HAVING SUM(tt_sales.amount) >= 4000 ORDER BY tm_prod.class;
この SQL の結果は以下の通りです。
+----------+------+-------+ | 商品分類 | 数量 | 金額 | +----------+------+-------+ | cre | 9 | 15000 | | pen | 58 | 4900 | +----------+------+-------+ 2 rows in set (0.02 sec)
■SQL文の中に WHERE句とHAVING句を共に使った場合について
集計 SQL では WHERE句 と HAVING句 を共に使った場合に、 先ず WHERE句 で対象となるデータを絞り込んで集計処理を行います。 その後で HAVING句 の条件を加味して最終的なデータが取得されます。
今回の例を示す前に「売上データ」の一覧を再度表示します。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)
WHERE句 での条件付けとして「売上日(salesdate)」の範囲を指定し、 さらに HAVING句 で「金額」の範囲を条件づけます。
「売上日(salesdate)」が「2024-01-05 ~ 2024-01-20」の範囲で、「金額」は4000円以上で、 以下の様な SQL となります。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 WHERE tt_sales.salesdate BETWEEN '2024-01-05' AND '2024-01-20' GROUP BY tm_prod.class HAVING SUM(tt_sales.amount) >= 4000 ORDER BY tm_prod.class;
この SQL の結果は以下の通りです。
+----------+------+-------+ | 商品分類 | 数量 | 金額 | +----------+------+-------+ | cre | 8 | 14000 | | pen | 48 | 4100 | +----------+------+-------+ 2 rows in set (0.03 sec)
PR -
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 の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 を使用したシステムに携わることが多く、Web系の仕事の場合にはほぼ100%に近い感じです。
MySQL は Oracle社 が開発したもので現在もメンテナンスがなされている様です。 また、Yahoo や Google などのシステムにも採用されていることからも、安心感もあります。
MySQL について20本ぐらいの記事を書いてきましたが、関数やSELECTなどについて個別の事例に付いてが多かったですが、 今回は少し毛色を変えて、初心者の方がテーブルの考え方で少参考になりそうな内容を記してみます。
以下の内容で、順を追って説明します。■テストテーブル(商品マスタ、売上データ)の作成について
いろんなWeb上のシステムがありますが、データ処理を行う時に、意味合いがまとまった情報を1個のデータ集合として考えます。 例えば、「個人情報」「会社情報」「商品情報」「売上データ」「売上統計データ」などが在ると思います。
それぞれを別のテーブルとして情報を保存しておきますが、 このなかで「個人情報」「会社情報」「商品情報」など1回データを作成すればあまり変化がない情報をマスタデータとして それぞれ別々のテーブルとして持つことが一般的です。 (あまり変化がないと言いましたが、それぞれの情報は更新できる必要があります。この作業をマスタメンテナンスと言います。)
ここでは、「商品情報」と「売上データ」に関してのみを例にして説明したいと思います。
「商品情報」(商品マスタと言い換えます)は以下の様な項目が必要だと思います。- 商品コード(商品の型番?)
- 商品名
- 単価
「売上データ」は以下の様な項目が必要だと思います。 ここでは、誰が買ったかなどの情報は割愛して考えますので、商品と売上のみの情報の項目のみとしています。 (後、消費税についても割愛します。)- 売上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), -- 商品コード 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)
-
データの編集などを含んだ SELECT文 を別のテーブルの様に扱えるのが ビュー(VIEW) で毎回同じ SELECT文 を記述しなくても 通常のテーブルの様に扱えます。
この ビュー(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)