sql loop and set properties
In SQL, I would like to query a list, in order by pageNumber
SELECT * FROM `comics`
WHERE 1
ORDER BY pageNumber ASC
Then, I would like to set their pageNumbers based on their index in the query (starting with 1 instead of 0).
Here is my pseudo code for the functionality as desired; where list is the return value of the Select Query above.
for(var n:int = 0; n<list.length; n++){
if(list[n].pageNumber != n+1){
开发者_Go百科 list[n].pageNumber = n+1
}
}
For example I might have pageNumbers 5, 17, 23, 24, 18, 7
The ORDER BY pageNumber ASC will sort this to 5, 7, 17, 18, 23, 24
I would then like to alter the pageNumbers in order to be 1, 2, 3, 4, 5, 6
edit:
@fortheworld MySQL
@cyberkiwi UPDATE
sorry for being unclear. guess i need to learn more for my questions to be clear :) thanks for all your help
SET @I := 0;
SELECT *,
@I := @I + 1 AS newPageNumber
FROM comics
ORDER BY pageNumber ASC
I don't understand why peops insist on writing an SQL batch when a single statement will do.
SELECT comics.*, @n := @n + 1 AS PageNumber2
FROM (SELECT @n := 0) X CROSS JOIN comics
ORDER BY pageNumber ASC
精彩评论