SQL 副問い合わせ

Question

以下のSQLでこのような期待値を取りたいのですが、うまく取れません。
解決方法を教えていただけると嬉しいです。

【結果】
"TESTGY01";2;"業務宛名担当者（管理者）_old";"ギョウムタントウシャ（カンリシャ）オールド"
"TESTGY01";2;"業務宛名担当者（管理者）";"ギョウムタントウシャ（カンリシャ）"

【期待値】
"TESTGY01";2;"業務宛名担当者（管理者）";"ギョウムタントウシャ（カンリシャ）"

【データ】
〇USER_AUTH
"TESTGY01";2;
"TESTGY02";1;

〇USER_NAME
"TESTGY01";1;"業務宛名担当者（管理者）_old";"ギョウムタントウシャ（カンリシャ）オールド";
"TESTGY01";2;"業務宛名担当者（管理者）";"ギョウムタントウシャ（カンリシャ）";

         SELECT
             UA.USER_ID AS "USER_ID"
           , UA.RIREKI_NUMBER AS "RIREKI_NUMBER"
           , UN.USER_NAME_KANJI AS "USER_NAME_KANJI"
           , UN.USER_NAME_KANA AS "USER_NAME_KANA"
         FROM
             USER_AUTH UA
         LEFT JOIN
	     USER_NAME UN
         ON
             UA.USER_ID = UN.USER_ID
         WHERE
           NOT EXISTS
             (
               SELECT
                 *
               FROM
                 USER_AUTH B
               WHERE
                   UA.USER_ID = B.USER_ID
               AND UA.RIREKI_NUMBER < B.RIREKI_NUMBER
             )
         AND UA.USER_ID = 'TESTGY01'

【補足】
試してみたSQLは以下です。

         SELECT
             UA.USER_ID AS "USER_ID"
           , UA.RIREKI_NUMBER AS "RIREKI_NUMBER"
           , UN.USER_NAME_KANJI AS "USER_NAME_KANJI"
           , UN.USER_NAME_KANA AS "USER_NAME_KANA"
         FROM
             USER_AUTH UA
         LEFT JOIN
             (
               SELECT
		     UN.USER_ID AS "USER_ID"
		   , UN.RIREKI_NUMBER AS "RIREKI_NUMBER"
		   , UN.USER_NAME_KANJI AS "USER_NAME_KANJI"
		   , UN.USER_NAME_KANA AS "USER_NAME_KANA"
               FROM
                 USER_NAME B
               WHERE
                   UN.USER_ID = B.USER_ID
               AND UN.RIREKI_NUMBER < B.RIREKI_NUMBER
             ) USER_NAME UN
         ON
             UA.USER_ID = UN.USER_ID
         WHERE
           NOT EXISTS
             (
               SELECT
                 *
               FROM
                 USER_AUTH B
               WHERE
                   UA.USER_ID = B.USER_ID
               AND UA.RIREKI_NUMBER < B.RIREKI_NUMBER
             )
         AND UA.USER_ID = 'TESTGY01'

追加
CREATE TABLE user_auth
(
user_id character varying(30) NOT NULL,
rireki_number integer NOT NULL DEFAULT 1,
CONSTRAINT pk_user_auth PRIMARY KEY (user_id, rireki_number)
)

CREATE TABLE user_name
(
user_id character varying(30) NOT NULL,
rireki_number integer NOT NULL DEFAULT 1,
user_name_kanji character varying(205),
user_name_kana character varying(205),
CONSTRAINT pk_user_name PRIMARY KEY (user_id, rireki_number)
)

〇USER_AUTH
INSERT INTO user_auth (user_id, rireki_number) VALUES ('TESTGY01', 1);
INSERT INTO user_auth (user_id, rireki_number) VALUES ('TESTGY02', 2);

〇USER_NAME
INSERT INTO user_name (user_id, rireki_number,user_name_kanji,user_name_kana) VALUES ('TESTGY01', 1,"業務宛名担当者（管理者）_old",ギョウムタントウシャ（カンリシャ）オールド);
INSERT INTO user_name (user_id, rireki_number,user_name_kanji,user_name_kana) VALUES ('TESTGY01', 2,"業務宛名担当者（管理者）",ギョウムタントウシャ（カンリシャ）);

Comments

[m.ts10806]

各テーブルのCREATEとサンプルデータのINSERTもご提示ください

[NEO_PLANETT-777]

条件何ですか？

[Orlofsky]

SQLで使われるテーブルはCREATE TABLEされていなければなりません。テーブル内のデータはINSERTされていなければSELECTできません。ですから、質問にCREATE TABLEやINSERTでデータを提示した方が適切な回答が付き易いです。

[merci_children]

失礼しました。
明日クリエイトテーブルとサンプルデータのインサートをあげさせていただきます。

[merci_children]

追加しました。恐れ入りますがよろしくお願いいたします。

Answer 1

結合条件にUA.RIREKI_NUMBER = UN.RIREKI_NUMBERを加えるだけです。
Existsの条件は不要です。
USER_AUTH のプライマリーが(USER_ID, RIREKI_NUMBER )なので訂正。

SELECT  UA.USER_ID, UA.RIREKI_NUMBER, UN.USER_NAME_KANJI, UN.USER_NAME_KANA
FROM  USER_AUTH UA
      LEFT JOIN USER_NAME UN
      ON    UA.USER_ID = UN.USER_ID 
        AND UA.RIREKI_NUMBER = UN.RIREKI_NUMBER
WHERE UA.USER_ID = 'TESTGY01'
  and not exists(
       select 1 from USER_AUTH 
       where USER_ID=UA.USER_ID 
         and RIREKI_NUMBER > UA.RIREKI_NUMBER
      )

※"を使用した項目名やエイリアスは大文字小文字を厳密に区別されるようになります。
意識しての事なら良いですけど、そうでないなら"は使用しない方が良いかと思います。

追記

RIREKI_NUMBERがそれぞれのテーブルごとのものの場合（最新のUSER_AUTHと最新のUSER_NAMEを結合したい）
は、not existsを同様に条件として追加すればOK

SELECT  UA.USER_ID, UA.RIREKI_NUMBER, UN.USER_NAME_KANJI, UN.USER_NAME_KANA
FROM  USER_AUTH UA
      LEFT JOIN (
        select *
        from USER_NAME UN
        where not exists(
                select 1 from USER_NAME
                where USER_ID=UN.USER_ID and RIREKI_NUMBER > UN.RIREKI_NUMBER
              )
      ) UN
      ON    UA.USER_ID = UN.USER_ID 
WHERE UA.USER_ID = 'TESTGY01'
  and not exists(
        select 1 from USER_AUTH 
        where USER_ID=UA.USER_ID and RIREKI_NUMBER > UA.RIREKI_NUMBER
      )

Comments

[merci_children]

遅くなってしまい、申し訳ございません。
回答ありがとうございます。追記のSQLを試した結果うまくいきました。

Answer 2

SELECT
auth.user_id as id,
last.rireki_number as rireki_no,
name.user_name_kanji as kanji,
name.user_name_kana as kana
FROM
user_auth as auth
INNER JOIN
user_name as name
ON auth.user_id = name.user_id,
(
SELECT
MAX(user_name.rireki_number) as rireki_number
FROM
user_name
) as last
WHERE
name.rireki_number = last.rireki_number
AND auth.user_id = 'TESTGY01'

---

上記の質問を踏まえて考えると以下のとおりであると想定しています。

user_authのuser_idに紐付いているuser_nameの最大履歴番号を取得したい。

->
user_nameの履歴番号は増える。
user_auth.rireki_numberは別の要件で使うかで今回は考慮しない。

思考手順としては、user_nameの最大履歴番号を取得してその値で絞り込むことで導出できるのではと考えました。

実行手順としては、
user_nameの最大履歴番号を取得するのに、MAX関数を利用しました。

https://www.postgresql.jp/document/11/html/tutorial-agg.html

• 利用されているversionをご確認ください。

初心者マークが付与されていることを鑑みて、ASは省略していません。

Answer 3

SELECT
  *
FROM
  USER_AUTH AS UA
  LEFT JOIN
    (SELECT USER_ID, MAX(RIREKI_NUMBER) AS 'RIREKI_NUMBER' FROM USER_NAME GROUP BY USER_ID) AS UN
  ON UA.USER_ID = UN.USER_ID AND UA.RIREKI_NUMBER = UN.RIREKI_NUMBER
  LEFT JOIN
    USER_NAME AS UN2
  ON UN.USER_ID = UN2.USER_ID AND UN.RIREKI_NUMBER = UN2.RIREKI_NUMBER
WHERE
  UA.USER_ID = 'TESTGY01'

Comments

[merci_children]

ちょっと時間をください。
SQLが不慣れなもので、理解するのに時間がかかってます。。。

Answer 4

単純に結合条件に履歴Noをいれてやればいいです。

SELECT
   UA.USER_ID AS "USER_ID"
  , UA.RIREKI_NUMBER AS "RIREKI_NUMBER"
  , UN.USER_NAME_KANJI AS "USER_NAME_KANJI"
  , UN.USER_NAME_KANA AS "USER_NAME_KANA"
FROM
  USER_AUTH UA
  LEFT JOIN
    USER_NAME UN
    ON UA.USER_ID = UN.USER_ID
    AND UA.RIREKI_NUMBER = UN.RIREKI_NUMBER // ←これを追加
WHERE
  NOT EXISTS (
    SELECT
      B.USER_ID
    FROM
      USER_AUTH B
    WHERE
      UA.USER_ID = B.USER_ID
      AND UA.RIREKI_NUMBER < B.RIREKI_NUMBER
  )
  AND UA.USER_ID = 'TESTGY01'

Comments

[merci_children]

これだとUSER_NAMEの履歴Noが3とかの場合にとってこれなくなっちゃいます。。。

[sazi]

not exists　部分は不要だと思いますけど、「履歴Noが3」が追加されると、USER_AUTHのRIREKI_NUMBERも3になるのではないですか？
そうでないなら、USER_AUTHのRIREKI_NUMBERは何を管理するのでしょうか？

[sazi]

USER_AUTHのRIREKI_NUMBERもプライマリーの一部なんですね・・・
そうなるとテーブルを分ける意味が不明ですけど

[macaron_xxx]

このテーブル構成からRIREKI_NOがそれぞれのテーブルで独立しているとは思いませんでした。

[macaron_xxx]

NOT EXISTS　をいれいるのは、もちろん履歴Noなんだから、最新のものを取る必要があると思っただけですね。
そうでないなら、履歴Noの意味…