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
| CREATE TABLE `TEST_PERE` (
`ID` INTEGER NOT NULL,
PRIMARY KEY(ID)
);
INSERT INTO TEST_PERE (ID) VALUES (340);
INSERT INTO TEST_PERE (ID) VALUES (350);
CREATE TABLE `TEST_FILS` (
`ID` INTEGER NOT NULL,
`RANK` INTEGER NOT NULL,
`PARENT` INTEGER NOT NULL,
PRIMARY KEY(ID)
);
INSERT INTO TEST_FILS (ID, RANK, PARENT) VALUES (341,1,340);
INSERT INTO TEST_FILS (ID, RANK, PARENT) VALUES (342,2,340);
INSERT INTO TEST_FILS (ID, RANK, PARENT) VALUES (343,3,340);
INSERT INTO TEST_FILS (ID, RANK, PARENT) VALUES (351,1,350);
INSERT INTO TEST_FILS (ID, RANK, PARENT) VALUES (352,2,350);
INSERT INTO TEST_FILS (ID, RANK, PARENT) VALUES (353,3,350);
SELECT
currentFils.ID,
currentFils.RANK,
ifnull((SELECT tmpFils.ID FROM TEST_FILS tmpFils WHERE currentFils.RANK - 1 = tmpFils.RANK AND currentFils.PARENT = tmpFils.PARENT),0) AS Previous
FROM TEST_FILS currentFils; |
Partager