SQLパズル11 作業依頼　解法1　メモ

Last updated at 2024-10-26Posted at 2024-10-26

概要

SQLパズル11 作業依頼の解法1についてのメモです。

サブクエリとALL部分が理解に手間取ったので記録に残しておきます。
理解するのにはサブクエリと自己結合とALL(限定述語)の知識が必要。

要件

以下条件を満たす作業依頼IDを求めたい：
イ.工程番号0が完了している(step_nbr=0 の step_status='C')
ロ.工程番号1~Nが未完了(サブクエリ部分)

2.の部分を理解するのに手間取ったので、実際にデータを見て細かい動きを確認してきます。

SQL

SELECT workorder_id
FROM projects AS P1
WHERE step_nbr = 0 AND step_status = 'C' -- 要件 イ
	AND 'W' = ALL (SELECT step_status -- 要件　ロ
					FROM projects AS P2
				  WHERE step_nbr <> 0
					AND P1.workorder_id = P2.workorder_id
				  );

求めたいレコード

SQL

SELECT workorder_id
FROM projects AS P1
WHERE step_nbr = 0 --←わかる
	AND step_status = 'C' -- わかる
	AND 'W' = ALL (SELECT step_status
					FROM projects AS P2
				  WHERE step_nbr <> 0
					AND P1.workorder_id = P2.workorder_id
				  )--わからない。サブクエリと'W'=ALLの部分
				  ;

1.サブクエリ部分-工程番号の組み合わせを作る

自己結合を使って作業ID毎の工程番号の組み合わせを求めます。
（工程番号0は含めない）

SELECT P1.*,P2.*
	FROM projects AS P2,projects AS P1 
  WHERE P2.step_nbr <> 0
	AND P1.workorder_id = P2.workorder_id;

画像

2.'W = ALL (サブクエリ) -上で求めた組み合わせの工程ステータスが”W”であるレコードを取得する。

あとは工程番号=0かつ工程ステータス=Cのレコードのみ取得すれば、完了。

SELECT P1.*
FROM projects AS P1
WHERE 'W' = ALL (SELECT P2.step_status
					FROM projects AS P2
				  WHERE P2.step_nbr <> 0
					AND P1.workorder_id = P2.workorder_id);