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(*)と同じ

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個が選択される

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
*/

こちらの記事もどうぞ

SQLSERVER パズル63　MINの扱いが難しく理解できない、相関サブクエリパズル63　問題文 dataのまとまりで連続しているnumを表示したい。 MINというと結 […]
P131 SQLパズルすべての製品を購入した人クエリの番号が画像のSELECT出力と同じはず。 /* P131 […]
SQLSERVER勉強中、SQL実践入門P255成績のところ　行を列に /* SQL実践入門P255成績のところを勉強中 */ 生徒に対して、複数の教科があり、生徒 […]
SQLSERVER　SELECT TOP にはできるだけORDER BYをつける先日、サーバによってSELECT TOP 1 AA FROM […]
SQLSERVER　行に出力されるanest_nameを横に1列に出力したいとき […]
SQLSERVER SUMの使い方GROUP SQLSERVER SUMの使い方GROUP /* […]

ピクチャ

ピクチャは海外ドラマ、漫画、英単語語呂合わせ法、アイフォンアプリ等の記事を紹介します

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

こちらの記事もどうぞ