SQL テーブルの結合について

以下のプログラムは年月と売上個数を出すものです。
このプログラムにさらに、翌月のデータ（年月200103、売上個数0）が表示されるようにしたいのですが、うまくできません。

yearテーブルとmonthテーブルを使って文字列を連結させればなんとかなりそうだとおもったのですが、できませんでした。

日付はchar型のままでやりたいです。

二つのテーブルをまたいで文字列を連結し、さらにその連結した文字列を用いて
ほかのテーブルと連結したいです。

以下のプログラムでは、おそらくLEFT JOIN以下が間違っていると思うのですが、どのようにすればよいでしょうか。

SELECT SUBSTR(ORDERS.DAY,1,4), NVL(SUM(ORDERS.CNT),0),
CONCAT(YEAR.VAL,MONTH.VAL)

FROM ORDERS LEFT JOIN YEAR, MONTH
ON SUBSTR(ORDERS.DAY,1,4) = CONCAT(YEAR.VAL, MONTH.VAL)

GROUP BY SUBSTR(ORDERS.DAY,1,4), CONCAT(YEAR.VAL, MONTH.VAL)

-- ORDERテーブル
CREATE TABLE ORDERS (
NO CHAR(4) NOT NULL PRIMARY KEY, -- 注文NO
DAY CHAR(8) NOT NULL, -- 日付
CODE CHAR(3) NOT NULL, -- 商品コード
CNT NUMBER(5) NOT NULL -- 個数
);

-- 年
CREATE TABLE YEAR (
VAL CHAR(4) NOT NULL PRIMARY KEY
);

-- 月
CREATE TABLE MONTH (
VAL CHAR(2) NOT NULL PRIMARY KEY
);

INSERT INTO ORDERS VALUES ('0001', '20001214', '002', 10);
INSERT INTO ORDERS VALUES ('0002', '20001214', '002', 15);
INSERT INTO ORDERS VALUES ('0003', '20001214', '003', 30);
INSERT INTO ORDERS VALUES ('0004', '20001215', '003', 12);
INSERT INTO ORDERS VALUES ('0005', '20010101', '001', 8);
INSERT INTO ORDERS VALUES ('0006', '20010101', '002', 11);
INSERT INTO ORDERS VALUES ('0007', '20010123', '002', 60);
INSERT INTO ORDERS VALUES ('0008', '20010205', '001', 36);

INSERT INTO YEAR VALUES ('2000');
INSERT INTO YEAR VALUES ('2001');

INSERT INTO MONTH VALUES ('01');
INSERT INTO MONTH VALUES ('02');
INSERT INTO MONTH VALUES ('03');
INSERT INTO MONTH VALUES ('04');
INSERT INTO MONTH VALUES ('05');
INSERT INTO MONTH VALUES ('06');
INSERT INTO MONTH VALUES ('07');
INSERT INTO MONTH VALUES ('08');
INSERT INTO MONTH VALUES ('09');
INSERT INTO MONTH VALUES ('10');
INSERT INTO MONTH VALUES ('11');
INSERT INTO MONTH VALUES ('12');

行動規範の内容に同意します

回答3件

ベストアンサー

こんにちわ。
作ってみました。

sql
1SELECT T.YYYYMM 年月, NVL(SUM(O.CNT), 0) AS 売上個数
2FROM (SELECT Y.VAL || M.VAL YYYYMM FROM YEAR Y CROSS JOIN MONTH M) T 
3LEFT OUTER JOIN ORDERS O ON T.YYYYMM = SUBSTR(O.DAY, 1 ,6)
4GROUP BY T.YYYYMM
5ORDER BY T.YYYYMM

投稿2015/08/04 02:17

退会済みユーザー

総合スコア0

qwerty123

2015/08/04 11:41

理解できました、みなさんありがとうございます！

行動規範の内容に同意します

要するに存在しない行を無理やり追加したいということでしょうか？

次のように 200103 の売上の行を追加して集計したり。

SELECT SUBSTR(ORDERS.DAY,1,6) AS 年月, NVL(SUM(ORDERS.CNT),0) AS 売上個数
FROM (
    SELECT * FROM ORDERS
    UNION ALL
    SELECT NULL, '20010300', NULL, 0 FROM DUAL
) ORDERS
GROUP BY SUBSTR(ORDERS.DAY,1,6)
ORDER BY SUBSTR(ORDERS.DAY,1,6)

集計した結果に 200103 を追加してみたり。

SELECT 年月, SUM(売上個数)
FROM (
    SELECT SUBSTR(ORDERS.DAY,1,6) AS 年月, NVL(SUM(ORDERS.CNT),0) AS 売上個数
    FROM ORDERS
    GROUP BY SUBSTR(ORDERS.DAY,1,6)
    UNION ALL
    SELECT '200103', 0 FROM DUAL
) X
GROUP BY 年月
ORDER BY 年月

ただ、SQLでどうにかするよりアプリケーション側でどうにかする方が良いと思いますけど(^_^;)

（MySQL で NVL を IFNULL に書き換えて試したので Oracle とかで動くかわかりません）

あぁ、こういうことですか。

SELECT
  CONCAT(YEAR.VAL,MONTH.VAL) AS 年月,
  NVL(SUM(ORDERS.CNT),0) AS 売上個数
FROM YEAR JOIN MONTH LEFT JOIN ORDERS
  ON  YEAR.VAL = SUBSTR(ORDERS.DAY,1,4)
  AND MONTH.VAL = SUBSTR(ORDERS.DAY,5,2)
GROUP BY
  年月

+--------+--------------+
| 年月   | 売上個数     |
+--------+--------------+
| 200001 |            0 |
| 200002 |            0 |
| 200003 |            0 |
| 200004 |            0 |
| 200005 |            0 |
| 200006 |            0 |
| 200007 |            0 |
| 200008 |            0 |
| 200009 |            0 |
| 200010 |            0 |
| 200011 |            0 |
| 200012 |           67 |
| 200101 |           79 |
| 200102 |           36 |
| 200103 |            0 |
| 200104 |            0 |
| 200105 |            0 |
| 200106 |            0 |
| 200107 |            0 |
| 200108 |            0 |
| 200109 |            0 |
| 200110 |            0 |
| 200111 |            0 |
| 200112 |            0 |
+--------+--------------+

投稿2015/08/04 01:29

編集2015/08/04 02:18