購入明細テーブルで顧客の古い履歴を求める
P202_SQL実践入門_購入明細で顧客の古い履歴を求める
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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 |