sql循环并设置属性 [英] sql loop and set properties

问题描述

在SQL中，我想按pageNumber顺序查询列表

In SQL, I would like to query a list, in order by pageNumber

SELECT * FROM `comics` 
WHERE 1
ORDER BY pageNumber ASC

然后，我想根据查询中的索引设置其pageNumbers(以1而不是0开头).

Then, I would like to set their pageNumbers based on their index in the query (starting with 1 instead of 0).

这是我针对所需功能的伪代码；其中list是上面的选择查询"的返回值.

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){
        list[n].pageNumber = n+1
    }
}

例如，我可能有pageNumbers 5, 17, 23, 24, 18, 7

For example I might have pageNumbers 5, 17, 23, 24, 18, 7

ORDER BY pageNumber ASC会将其排序为5, 7, 17, 18, 23, 24

The ORDER BY pageNumber ASC will sort this to 5, 7, 17, 18, 23, 24

然后我想更改pageNumber以便成为1, 2, 3, 4, 5, 6

I would then like to alter the pageNumbers in order to be 1, 2, 3, 4, 5, 6

@fortheworld MySQL

@fortheworld MySQL

@cyberkiwi更新

@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

这篇关于sql循环并设置属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！