購入明細テーブルで顧客の古い履歴を求める

P202_SQL実践入門_購入明細で顧客の古い履歴を求める

CREATE TABLE Receipts(
cust_id Char(1) NOT NULL,
seq Int NOT NULL,
price int NOT NULL

)


INSERT INTO Receipts VALUES('A',1,500),
('A',2,500),
('A',3,500),
('B',4,100),
('B',5,100),
('B',6,100),
('B',7,100),
('B',8,100),
('C',9,100),
('C',10,100),
('C',12,100),
('C',13,100),
('D',3,100)

SELECT * FROM Receipts
/*
cust_idをグループにして
Window関数にする
*/

	SELECT cust_id,seq,price,
	ROW_NUMBER() OVER (PARTITION by cust_id ORDER BY seq) as ROW_SEQ
	FROM Receipts 


SELECT * FROM (--サブクエリにすることでROW_SEQがWHERE条件につけられる。
				--SELECT句に別名をつけてもWHERE句では認識できない
	SELECT cust_id,seq,price,
	ROW_NUMBER() OVER (PARTITION by cust_id ORDER BY seq) as ROW_SEQ
	FROM Receipts ) 
	 as Work 
	WHERE
	work.row_seq = 1