SQLにて、特定条件レコードの有無を取得条件にする方法

Question

お世話になっております。

以下のようなデータをもつテーブルがあります。

id,name,dept,level
1,yamada,A1,10
2,tanaka,A2,10
3,suzuki,A3,10
4,kobayashi,A4,10
5,satou,A4,20

この時に、
同じdeptに異なるlevelの複数idが存在する場合、
levelが20のレコードを優先する形で抽出。
存在しない場合、levelが10のレコードを抽出するようにしたいです。

求める結果
id,name,dept,level
1,yamada,A1,10
2,tanaka,A2,10
3,suzuki,A3,10
5,satou,A4,20

上手い方法がありましたらご教示ください。

宜しくお願い致します。

Comments

[maisumakun]

dept,levelの2つセットで考えた場合は、レコードはUNIQUEということで間違いないでしょうか。

[emeraldworm]

ご返信ありがとうございます。deptとlevelはuniqueではありませんので、同dept同levelが複数存在することはあり得ます。

[maisumakun]

同じdeptにlevelが20のレコードが複数あった場合、どれを抽出すればいいのでしょうか。

[emeraldworm]

ご返信ありがとうございます。その場合は、20のレコード全てが抽出されてほしいです。20がなく、10が複数の場合も同様になります。

Answer 1

相関問い合わせで、「自身のID以外で、deptが同じ、かつlevel=20のデータが存在しないもの」

select * from テーブル t1
where not exists(
        select 1 from テーブル 
        where id<>t1.id and dept=t1.dept and level=20 
      )

Comments

[emeraldworm]

ご回答ありがとうございました。皆様から頂いたSQLを基に、無事望むデータが抽出できました。

Answer 2

levelは10と20しか存在しないのでしょうか？
仮に10と20と30があっても20を優先したいのでしょうか？
それとも単に最大値のレコードを抽出したいのでしょうか？

とりあえず最大値のレコードを含むものを抽出する方法

• 元データ

create table tbl(id int unique,name varchar(20),dept varchar(10),level int);
insert into tbl values
(1,'aaa','A1',10),
(2,'bbb','A2',10),
(3,'ccc','A2',10),
(4,'ddd','A3',10),
(5,'eee','A3',10),
(6,'fff','A3',20),
(7,'ggg','A4',10),
(8,'hhh','A4',20),
(9,'iii','A4',20),
(10,'jjj','A5',20),
(11,'kkk','A5',20),
(12,'lll','A6',20);

• 抽出

select * from tbl as t1 
where not exists
(select 1 from tbl where dept=t1.dept and level>t1.level)

Comments

[emeraldworm]

ご回答ありがとうございました。皆様から頂いたSQLを基に、無事望むデータが抽出できました。

Answer 3

条件を抽出した上でJOINしましょう。

SELECT main_table.* FROM table_name AS main_table
INNER JOIN (
  SELECT dept, MAX(level) AS max_level FROM table_name GROUP BY dept
) AS cond
ON main_table.dept = cond.dept AND main_table.level = cond.max_level

Comments

[emeraldworm]

ご回答ありがとうございました。皆様から頂いたSQLを基に、無事望むデータが抽出できました。