複数条件を組み合わせてデータを取得したい

Question

以下の項目があるテーブルから指定条件でデータを抽出したいです。

抽出後項目
・学籍番号 number(5)
・氏名 verchar2(50)
・科目 verchar2(50)
・試験日 verchar2(10) ← yyyy/mm/dd
・試験回数 number(3)
・結果 verchar2(1) ← 合否を0,1で表現
・ログインユーザ名（ユーザ情報テーブルから）

テーブル「試験結果データ」
・学籍番号 number(5) PK
・氏名 verchar2(50)
・科目 verchar2(50)
・試験日 verchar2(10) ← yyyy/mm/dd
・試験回数 number(3) ← 試験日毎にカウント
・結果 verchar2(1) ← 合否を0,1で表現
・科目担当者（ログインユーザコード）

テーブル「ユーザ情報」
・ユーザコード number(5) PK
・ユーザ名 verchar2(50)
・ユーザ使用期間FROM verchar2(10) ← yyyy/mm/dd
・ユーザ使用期間TO verchar2(10) ← yyyy/mm/dd

SELECT条件
・現在ログインしているログインユーザ
・試験日のMAX
・回数のMAX

補足
・ログインユーザは、Javaから取得しています。
・ログインユーザ = ユーザ情報.ユーザコードです。
・PreparedStatementを利用しています。
・1回のSQL文で抽出したいです。
・副問い合わせやFROM句のサブクエリ、JOINなど方法は問いませんが、
複数の回答があると実際の仕様に応じて対応しやすいので助かります。
・PCの時刻をJavaで取得して、ユーザ情報テーブルの使用期間内であるかを条件に加えたいです。
・使用期間内について、ユーザ情報テーブルに各ユーザのライセンス情報をFROM–TOで登録してあり、本来であれば不正なデータが存在した場合に弾くための対応です。

条件・制約が多く申し訳ありませんが、ご教授のほどよろしくお願いいたします。

Comments

[dupont_kedama]

テーブルの情報が不足しています。
サンプルレコードとその場合の期待する結果があると答えやすいと思います。
特に「試験結果データ」テーブルにどのようなレコードが入るのかが想像しづらいです。
主キー（やユニーク制約）があるならそれも付記いただければと思います。
サンプルレコードで表現いただければ構いませんが、「使用期間内」の場合、「ユーザ使用期間TO」には何が入るのかもわかりません。

Answer 1

副問い合わせ部分ではROW_NUMBER分析関数を使ってユーザーコードごとにグループ化し、
試験日の大きいもの、試験回数の多いもの順にナンバリングします。
NOの値1は試験日のMAX、試験回数のMAXを意味しますので、
外側で[WHERE NO = 1]としています。

※ユーザコードをWHERE句で絞り込んでいますので、
[PARTITION BY USR.ユーザコード]はなくても構いません。

SELECT 学籍番号,
	氏名,
	科目,
	試験日,
	試験回数,
	結果,
	ユーザ名
FROM
(
	SELECT RES.学籍番号,
		RES.氏名,
		RES.科目,
		RES.試験日,
		RES.試験回数,
		RES.結果,
		USR.ユーザ名,
		ROW_NUMBER() OVER(PARTITION BY USR.ユーザコード ORDER BY 試験日 DESC, 試験回数 DESC) AS NO
	FROM ユーザ情報 USR,
		試験結果データ RES
	WHERE USR.ユーザコード = RES.科目担当者
	AND USR.ユーザコード = :ログインユーザ
)
WHERE NO = 1;

Comments

[nabe3]

上記回答はご質問内容が編集される前のものでした。

追加された要件、
> ・使用期間内について、ユーザ情報テーブルに各ユーザのライセンス情報をFROM–TOで登録してあり、本来であれば不正なデータが存在した場合に弾くための対応です。

についての対応です。
単に結果を返さなくてよければ、副問い合わせ内のWHERE条件に、
```SQL
AND :PCの時刻 BETWEEN USR.ユーザ使用期間FROM AND ユーザ使用期間TO
```
を追加すれば良いです。

ライセンス使用期間に反するかどうかを結果として返したい場合は、
このSQLとは別に単独のSQLでテーブル「ユーザ情報」に対して
チェックをかけたほうが良いと思います。

[ozaking]

コメントが遅くなり申し訳ありません。
回答ありがとうございます。
ひとまず独学でSQL完成しましたが、性能等は考えられなかったため、参考までに回答欄に記載いたします。
修正箇所など気になる点がありましたら、ご意見頂戴したく思います。
（カラム名を日本語に置き換えたため、多少の構文ミスがあるかと思います。ご容赦ください。）

Answer 2

 SELECT 
   test4.試験日 
   , test4.試験回数 
   , test4.学籍番号 
   , test4.氏名 
   , test4.科目 
   , test4.結果 
   , user.ログインユーザ名  
 FROM 
   (  
     SELECT 
       test2.ログインユーザ 
       , test2.試験日 
       , MAX(test2.試験回数) AS 試験回数  
     FROM 
       (  
         SELECT 
           ログインユーザ 
           , MAX(試験日) AS 試験日  
         FROM 
           試験結果データテーブル  
         WHERE 
           ログインユーザ = 'ログインユーザコード'  
         GROUP BY 
           ログインユーザ 
       ) test1 
       , 試験結果データテーブル test2  
     WHERE 
       test1.ログインユーザ = test2.ログインユーザ  
       AND test1.試験日 = test2.試験日  
     GROUP BY 
       test2.ログインユーザ 
       , test2.試験日 
   ) test3 
   , 試験結果データテーブル test4 
   , ユーザ情報テーブル user 
 WHERE 
  test3.ログインユーザ = test4.ログインユーザ  
   AND test3.試験日 = test4.試験日  
   AND test3.試験回数 = test4.試験回数  
   AND test4.ログインユーザ = user.ログインユーザ  
   AND user.試用期間FROM <= test4.試験日  
   AND user.試用期間TO >= test4.試験日