SQLのコードを短くしたい

Question

前提・実現したいこと

お世話になっております。
SQLで苦戦しております。
自分でコードを書いて、動くことを確認したのですが、冗長に感じています。。。
短くする（※アルゴリズムの変更も含む）ことは可能でしょうか？

以下問題のテーブル（studentとtest）とレコードの図です。

student

---

id student_name

---

001 杏子
002 明日香
003 亜紀
004 小鳥
005 柚子
006 葵

---

test

---

id_subject result

---

001国語 65
002国語 70
002数学 50
003生物 100
003倫理 45
003数学 75
004物理 30
006地学 55

---

発生している問題・エラーメッセージ

コードは動くが、冗長に感じるため短くしたい

問題：1番高い点数を持つ生徒名とその点数、1番低い点数を持つ生徒名とその点数を出すSQL

結果

---

｜小鳥　｜　30 ｜

---

｜亜紀 ｜　100 ｜

---

該当のソースコード

SELECT s.id,t.result
FROM student s
JOIN test t
ON s.id=SUBSTR(t.id_subject,1,3)
LEFT JOIN(SELECT MAX(t.result)AS value FROM test t)max
ON max.value=t.result
LEFT JOIN(SELECT MINX(t.result)AS value FROM test t)min
ON min.value=t.result
WHERE max.value IS NOT NULL OR min.value IS NOT NULL;

試したこと

データを抜き出せることは確認しました。

補足情報（FW/ツールのバージョンなど）

SQLplus, Oracle11g

Comments

[yambejp]

id_subjectがstudentのidと教科を連結したようなデータになっていますがマストですか？カラムを分けていいならもっと楽だと思いますが・・・

[sky.user.]

ご回答ありがとうございます。そうですね…今のテーブル、レコードがこの形なのでこのまま使いたいと思っています。

[yambejp]

「このまま使いたい」というのでとめはしませんが、パフォーマンスは期待できませんのであしからず。回答付けておきました

[Orlofsky]

yambejpさんと同意見です。データが増えるとパフォーマンスは極端に悪くなりますし、今後も複雑なSQLを書き続けなければなりません。わたしの部下ならプロジェクトから外すか、殴り倒してでも直させます。

[sazi]

普通科目ごとの最高/最低の点数だと思いますけど、科目によらず最高/最低なんでしょうか？

[Orlofsky]

[Oracle]タグを使いしてください。

Answer 1

図でイメージするOracleのSQL全集 第3回 分析関数
https://blogs.oracle.com/otnjp/oracle-sql-image-003
を見ながら作ってみました :-)

with student(id,student_name) as(
select '001','杏子'   from dual union
select '002','明日香' from dual union
select '003','亜紀'   from dual union
select '004','小鳥'   from dual union
select '005','柚子'   from dual union
select '006','葵'     from dual),
test(id_subject,result) as(
select '001国語', 65 from dual union
select '002国語', 70 from dual union
select '002数学', 50 from dual union
select '003生物',100 from dual union
select '003倫理', 45 from dual union
select '003数学', 75 from dual union
select '004物理', 30 from dual union
select '006地学', 55 from dual),
tmp as(
select id_subject,result
from (select id_subject,result,
      min(result) over() as minResult,
      max(result) over() as maxResult
      from test)
 where result in(minResult,maxResult))
select a.id,a.student_name,b.result
  from student a Join tmp b
    on a.id = substr(b.id_subject,1,3);

|  ID | STUDENT_NAME | RESULT |
|-----|--------------|--------|
| 003 |         亜紀 |    100 |
| 004 |         小鳥 |     30 |

Answer 2

1つ列には1つ意味しか持たせてはならない、というテーブル設計の基本を守らないからです。
testテーブルのid_subjectにstudent.id と教科名を合体させているのが敗因です。

CREATE TABLE SUBJECTS  -- 教科テーブル
    (
    SUBJECT_ID    NUMBER(3)     NOT NULL  -- 教科ID
  , SUBJECT_NAME  VARCHAR2(32)  NOT NULL  -- 教科名
  , CONSTRAINT SUBJECTS_PK PRIMARY KEY(SUBJECT_ID) USING INDEX
    ) ;

test テーブル(生徒履行テーブル)には STUDENT.ID, SUBJECT_ID, RESULT を持てば、SELECT文は自分で直せるかと。

テーブル定義はCREATE TABLEで載せると解決が早いです。
テーブル名や列名のネーミングには気を使いましょう。

Answer 3

oracleで有効かどうかはわかりませんが一応こんなかんじで

select t2.student_name,t1.result from test as t1
inner join student as t2
on substr(t1.id_subject,1,3)=t2.id
where not exists(select 1 from test having t1.result>min(result))
or not exists(select 1 from test having t1.result<max(result))

ただし最高点や最低点が2人以上いるときにどうしたいかなど
仕様をつめないといけません

調整

select t2.student_name,t1.result from test as t1
inner join student as t2
on substr(t1.id_subject,1,3)=t2.id
where not exists(select 1 from test 
having t1.result>min(result)
and  t1.result<max(result))

Comments

[sazi]

副問合せのhavingでの条件を最大または最小にしたら、副問合せを一つに纏められそう

[sazi]

あれ、副問合せにidでの条件が無いですね

[sazi]

あ、最高点と最低点の生徒か。普通学科ごとな気がしますけどね。

[yambejp]

saziさんにご指摘いただき、よく考えればandでまとめられましたので追記しました

[sazi]

t1.result=min(result) or t1.result=max(result)
では？

[yambejp]

ああ、そうですね・・・