SQLSERVER クエリ、ALL演算子、puzzel11_作業依頼

/*
puzzel11_作業依頼
C:完了
W:待機
step_nbrが0でstep_statusがCでWであることを探す。
するとAA100が該当する
*/

workorder_id step_nbr step_status
AA100 0 C
AA100 1 W
AA100 2 W
AA200 0 W
AA200 1 W
AA300 0 C
AA300 1 C

ALL演算子を使う。ALLとあるけどSUMではなくて、条件から出力する

CREATE TABLE Projects(
workorder_id char(5) NOT NULL,
step_nbr int NOT NULL,
step_status char(1) NOT NULL
)

INSERT INTO Projects VALUES('AA100',0,'C'),
('AA100',1,'W'),
('AA100',2,'W'),
('AA200',0,'W'),
('AA200',1,'W'),
('AA300',0,'C'),
('AA300',1,'C')

--



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--ALLは条件に合う結果を抽出する
					WHERE step_nbr <> 0
					and P1.workorder_id = P2.workorder_id)



SELECT workorder_id FROM Projects
GROUP BY workorder_id
HAVING COUNT(*) = COUNT(CASE WHEN step_nbr = 0 AND step_status = 'C'--1になる
							THEN 1
							ELSE NULL END)
				+
				COUNT(CASE WHEN step_nbr <> 0 AND step_status= 'W'--2になる
							THEN 1
							ELSE NULL END)
			--加算すると3になってCOUNT(*)と同じ

/*
ALL比較演算子の勉強

参考サイト
http://www.sql-reference.com/select/subquery_all.html

*/

Create Table allTable(
orderNum nvarchar(20) NOT NULL,
productCode nvarchar(10) NOT NULL,
Num int NOT NULL
)

INSERT INTO allTable VALUES('01-101','A001',100),
('01-102','A002',200),
('01-103','B001',300),
('01-104','B002',400),
('02-101','A001',150),
('02-102','A002',350)

SELECT * FROM allTable
WHERE Num > ALL (
    SELECT Num
    FROM allTable
    WHERE productCode = 'A002' )--ALLでは200,350が抽出される。そして一番大きな400個が選択される

/*
結果
orderNum productCode Num
01-104 B002 400
*/

こちらの記事もどうぞ