質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.82%

SQLで日付範囲指定のある場合の結合方法について

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 2,565

hatsuzo

score 22

いつもお世話になっております。
Oracleを使用しているのですが、Webアプリで仕訳元帳の照会を行うことになりました。

仕訳データの中に入っている勘定科目と補助科目の科目名を表示したいのですが、
SQL文で、

SELECT a.勘定科目CD, a.補助科目CD, b.勘定科目名, c.補助科目名
FROM 仕訳 a 
LEFT JOIN 勘定科目 b ON a.勘定科目CD = b.勘定科目CD
LEFT JOIN 補助科目 c ON a.勘定科目CD = c.勘定科目CD AND a.補助科目CD = c.補助科目CD
WHERE a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日
AND a.計上日 >= c.有効開始日 AND a.計上日 < c.有効終了日;


とした時、全ての勘定科目に補助科目があるとすれば問題ないと思うのですが、当然ながら、勘定科目の中には補助科目を持たないものも存在します。
このため、WHERE文で、補助科目の無い場合を想定して、下記のように書いていますが、WHERE文が複雑だと処理効率が悪すぎることになります。

SELECT a.勘定科目CD, a.補助科目CD, b.勘定科目名, c.補助科目名
FROM 仕訳 a 
LEFT JOIN 勘定科目 b ON a.勘定科目CD = b.勘定科目CD
LEFT JOIN 補助科目 c ON a.勘定科目CD = c.勘定科目CD AND a.補助科目CD = c.補助科目CD
WHERE (a.補助科目 is not NULL AND a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日
AND a.計上日 >= c.有効開始日 AND a.計上日 < c.有効終了日) OR
(a.補助科目 is NULL AND a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日)


基本的なことで大変申し訳ないのですが、
こうした場合、LEFT JOIN の中に日付の結合条件を書けば同じことになるのでしょうか?

LEFT JOIN 勘定科目 b ON a.勘定科目CD = b.勘定科目CD
AND a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日
LEFT JOIN 補助科目 c ON a.勘定科目CD = c.勘定科目CD AND a.補助科目CD = c.補助科目CD
AND a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日
AND a.計上日 >= c.有効開始日 AND a.計上日 < c.有効終了日
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Orlofsky

    2019/05/31 18:04

    質問にCREATE TABLE文、何件かデータのINSERT文と希望するSELECT結果も載せてください。

    キャンセル

  • hatsuzo

    2019/06/01 08:36

    コメントありがとうございます。
    文が長くなるので敢えて表記しませんでしたが、
    列名と表名は日本語表記、
    日付の書き方は正しい表記ではないと思いますが、構造の判る範囲で
    簡略化して書きました。

    CREATE TABLE 仕訳 (
    伝票No char(10) PRIMARY KEY,
    行No number(2) PRIMARY KEY,
    貸借 char(1),
    計上日 date,
    勘定科目CD varchar2(5),
    勘定科目CD varchar2(5),
    金額 date
    );

    CREATE TABLE 勘定科目 (
    勘定科目CD varchar2(5) PRIMARY KEY,
    有効開始日 date PRIMARY KEY,
    有効終了日 date PRIMARY KEY,
    勘定科目CD varchar2(30)
    );

    CREATE TABLE 補助科目 (
    勘定科目CD varchar2(5) PRIMARY KEY,
    補助科目CD varchar2(5) PRIMARY KEY,
    有効開始日 date PRIMARY KEY,
    有効終了日 date PRIMARY KEY,
    補助科目名 varchar2(30)
    );


    仕訳

    INSERT ALL
    INTO 仕訳 VALUES('0000000001', 1, '1', 2019/05/28, '1111', '1', 1500)
    INTO 仕訳 VALUES('0000000001', 1, '2', 2019/05/28, '4444', '', 1500)
    SELECT * FROM DUAL;

    勘定科目

    INSERT ALL
    INTO 勘定科目 VALUES('1111',1900/1/1,2999/12/31,'現金')
    INTO 勘定科目 VALUES('1112',1900/1/1,2999/12/31,'普通預金')
    INTO 勘定科目 VALUES('4444',1900/1/1,2018/12/31,'旅費')
    INTO 勘定科目 VALUES('4444',2019/1/1,2999/12/31,'旅費交通費')
    SELECT * FROM DUAL;

    補助科目

    INSERT ALL
    INTO 補助科目 VALUES('1111','1', 1900/1/1,2019/9/30,'小口現金')
    INTO 補助科目 VALUES('1111','1', 2019/10/1,2999/12/31,'小口現金(1)')
    INTO 補助科目 VALUES('1112','1', 1900/1/1,2999/12/31,'三菱UFJ銀行')
    SELECT * FROM DUAL;


    結果はこんなイメージです。

    '1111','現金', '1', '小口現金'
    '4444','旅費交通費', '', ''

    キャンセル

  • Orlofsky

    2019/06/01 09:30

    [質問]に追記してください。

    キャンセル

  • Orlofsky

    2019/06/01 09:35

    PRIMARY KEYの書き方はきちんとしてください。
    , CONSTRAINT PRIMARY_KEY_NAME1 PRIMARY KEY(COL1, COL2) USING INDEX

    キャンセル

回答 2

checkベストアンサー

0

LEFT JOIN の中に日付の結合条件を書けば同じことになるのでしょうか?

同じ事というより外部結合の場合は結合条件に含めた方が記述はすっきりします。

SELECT a.勘定科目CD, a.補助科目CD, b.勘定科目名, c.補助科目名
FROM  仕訳 a 
      LEFT JOIN 勘定科目 b 
      ON    a.勘定科目CD = b.勘定科目CD 
        AND a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日
      LEFT JOIN 補助科目 c 
      ON    a.勘定科目CD = c.勘定科目CD AND a.補助科目CD = c.補助科目CD 
        AND a.計上日 >= c.有効開始日 AND a.計上日 < c.有効終了日


※有効終了日の条件は以下にならなくて良いのでしょうか?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/06/01 08:51

    コメントありがとうございました。
    結合条件で書いてしまえばよいのですね。今まで不効率な書き方をしてきたような気がします。(汗)
    aとbの有効日の結合条件は、2つ目の結合では書く必要が無い、というか書いたら変ということですね。

    キャンセル

  • 2019/06/01 10:32

    クエリービルダーのようなGUIを使用すると、結合条件は=以外は表現できず、whereで記述するしかありませんので、そういうものだと思われている方はおられますね。

    キャンセル

  • 2019/06/05 17:12

    お恥ずかしいですが、まさにその通りです。

    キャンセル

0

そんな時には SELECT句のサブクエリはいかがでしょう。
こんな感じで

SELECT a.勘定科目CD, a.補助科目CD, b.勘定科目名, 
(
    SELECT TOP(1) c.補助科目名
    FROM 補助科目 c
    WHERE a.勘定科目CD = c.勘定科目CD AND a.補助科目CD = c.補助科目CD
      AND a.計上日 >= c.有効開始日 AND a.計上日 < c.有効終了日
) 補助科目名
FROM 仕訳 a 
LEFT JOIN 勘定科目 b ON a.勘定科目CD = b.勘定科目CD
WHERE a.計上日 >= b.有効開始日 AND a.計上日 < b.有効終了日;


遅いときは WITH 句を使うといいです。
以下蛇足です。
財務系は古い環境が残っていることが多く、インデックスなども変更できないケースが多々あると存じます。
場合によっては、SELECT句にサブクエリを書くと(キャッシュが効かず)大きくパフォーマンスを落とす可能性があります。
そんな時に手軽にキャッシュを効かせやすくする手法の一つとしてWITH句を追加する方法があります。

WITH cache AS
(
    SELECT 勘定科目CD
          ,補助科目CD
          ,補助科目名
          ,有効開始日
          ,有効終了日
    FROM 補助科目
)


サブクエリの補助科目テーブルの代わりにcacheを使ってください。
ただし通常はインデックスが無駄になりますので有害です。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/06/01 10:45

    WITH句は補助科目テーブルの必要項目だけを抜き出したキャッシュとして利用するだけで、
    サブクエリの構文はほぼそのままです。
    古いDBMSではSELECT句にサブクエリを書くとパフォーマンスが大きく落ちる場合があるので、割と一般的な使い方でした。
    最近のDBMSではあまり関係ないので蛇足的に書きましたが、それ自体蛇足だったかもしれません。

    キャンセル

  • 2019/06/01 14:19

    @hihijijiさん
    with使うことによるパフォーマンスについては、コメントしていません。
    回答されているのは相関サブクエリーなので、withへ置き換えできないのでは?という事です。
    手間でなければ、with使用したSQLを追記して貰えると助かります。

    キャンセル

  • 2019/06/03 11:33 編集

    @hihijijiさん
    追記、ありがとうございます。
    そのcatchをサブクエリー内の補助科目に置き換えるという事ですね。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.82%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る