SQLの絞込みの条件について

Question

SQLで複数のテーブルをJOINさせた時の表示の仕方で悩んでいます。
JOINの結果で以下のような1の結果が返ってきたときに、2のように各社員コードの年度の最大かつ期の最大で絞込みをしたいのですが、GROUP BYを使用してもうまく実現出来ません。
よろしければご教示願います。

DBはORCLEの11gになります。

1.JOINの取得結果
社員コード 社員名 年度 期
100 山田 2015 1
200 田中 2015 0
300 佐藤 2015 0
100 山田 2015 0
200 田中 2015 1
200 佐藤 2014 1

2.求めたい結果としては、以下の結果になります。
社員コード 社員名 年度 期
100 山田 2015 1
300 佐藤 2015 0
200 田中 2015 1

Comments

[unau]

結果としてどのような抽出になればいいのか、具体例を示していただけると曖昧性が減って回答しやすいと思います。

[haru07]

求めたい結果の追記を致しました。ありがとうございます。

[unau]

社員コードで GROUP BY して、その中で「年度 + 期」(たとえば年度が 2015 で期が 1 なら 20151 という数値として捉える) が最大になるレコードを抽出する、という理解でよいでしょうか。

Answer 1

社員コード → 社員名
という関数従属性があれば、これでよさそうですね。

select 社員コード,社員名,
max(年度) as 年度,
max(期) Keep(Dense_Rank Last order by 年度) as 期
  from JOINした結果
group by 社員コード,社員名;

Comments

[haru07]

回答ありがとうございます。副問い合わせとKeep(Dense_Rank Last order by)で求めたい結果が得られました。

Answer 2

KEEP(DENSE_RANK LAST)とGROUP BYを使う方法か、またはROW_NUMBER()で順番を付けた副問合せと結合して1番目だけ抽出する方法はいかがでしょうか。
SELECT項目が多い場合は、後者の方法の方が使い勝手が良いですが、「JOINの結果に対して絞込はしたい」という制約があると(いじれないビューになってるとかで)、前者の方法でしょうか。
KEEP(DENSE_RANK LAST)はORDER BY(年度と期の昇順)でソートした最後の行(つまり年度と期が最大の行)に対してMAX()をとっています。ORDER BYでソート順が一意に決まるならMAX()でもMIN()でも結果は同じです。

SELECT r.syain_id                                  AS "社員コード",
       s.syain_name                                AS "社員名",
       MAX(r.nendo) KEEP (DENSE_RANK LAST
                          ORDER BY r.nendo, r.ki)  AS "年度",
       MAX(r.ki)    KEEP (DENSE_RANK LAST
                          ORDER BY r.nendo, r.ki)  AS "期"
FROM rireki r
INNER JOIN syain s
ON s.syain_id = r.syain_id
GROUP BY r.syain_id,
         s.syain_name
ORDER BY r.syain_id

WITH
  sorted_rireki AS (
    SELECT r.syain_id,
           r.nendo,
           r.ki,
           ROW_NUMBER() OVER(PARTITION BY r.syain_id
                            ORDER BY r.nendo DESC, r.ki DESC) AS row_num
    FROM rireki r
  )
SELECT sr.syain_id   AS "社員コード",
       s.syain_name  AS "社員名",
       sr.nendo      AS "年度",
       sr.ki         AS "期"
FROM sorted_rireki sr
INNER JOIN syain s
ON s.syain_id = sr.syain_id
WHERE sr.row_num = 1
ORDER BY sr.syain_id

Comments

[haru07]

回答ありがとうございます。副問い合わせとKeep(Dense_Rank Last order by)で求めたい結果が得られました。

Answer 3

テーブル定義が不明なので、想像で書いてみました。
社員マスタテーブルには社員番号（社員コード）と社員名が、
年度マスタには社員番号（社員コード）、年度、期がある。
といったテーブル定義でやっています。

年度の最大だして、その後、最大年度内の期の最大とってあげればいいと思います。

oracle手元にないので、試してないですがおそらく行けると思います。

select
	s.社員番号
	, s.社員名
	, n1.年度
	, max(n2.期)
from
	社員マスタ s
inner join
	(
	select
		社員番号 num
		, max(年度) 年度
	from 
		年度
	group by
		社員番号
	) n1
	on
		s.社員番号 = n1.num
inner join
	年度 n2
	on
		s.社員番号 = n2.社員番号
	and
		n1.年度 = n2.年度
group by
	s.社員番号
	, s.社員名
	, n1.年度

Comments

[haru07]

回答ありがとうございます。副問い合わせとKeep(Dense_Rank Last order by)で求めたい結果が得られました。

Answer 4

SELECT * FROM TABLES ORDER BY 年数, 期 DESC

Comments

[haru07]

JOINの結果に対して絞込はしたいので上記のSQLは使えません。申し訳ございません。
質問が分かりづらかったので修正致しました。回答ありがとうございます。

Answer 5

Oralce は 10 年以上触っていないので忘れましたが、SQL においては 一般的に GROUP BY で集計して集計関数で計算した場合、集計したグループにおける平均値、最大値、最小値などしかわかりません。つまり、どのレコードが最大値だったのか、は集計関数を使ってもわかりません。
各グループにおける最大値のレコードを特定するためには副問合せが不可欠（のはず）です。

ご質問のケースで望ましい結果、やりたいことがはっきりわからないので具体的な SQL は書けませんが、考え方としては同一グループの中で最大のレコードを取得する SQL を書くということになります。

---

追記

こんな感じになるのかな、というのを書いてみました。Oracle が手元にないので未検証です。また、テーブル定義もわからないので、適当に想像して書いています。

SELECT outer_a.*, outer_b.*, outer_b.年度 * 10 + outer_b.期 AS outer_sortkey 
FROM table_a AS outer_a
INNER JOIN table_b AS outer_b ON outer_a.社員コード = outer_b.社員コード
WHERE NOT EXIST (
  SELECT inner_b.年度 * 10 + inner_b.期 AS inner_sortkey
  FROM table_a AS inner_a
  INNER JOIN table_b AS inner b ON inner_a.社員コード = inner_b.社員コード
  WHERE outer_a.社員コード = inner_a.社員コード
  AND outer_sortkey < inner_sortkey
);