mysql增量查询_mysql – 基于GROUP BY添加增量字段的查询

一个)

您可以使用随每行递增的变量,并使用每个user_ID重置以获取行计数.

SELECT ID,User_ID,`Order`

FROM ( SELECT @r:= IF(@u = User_ID,@r + 1,1) AS `Order`,ID,@u:= User_ID

FROM Photos,(SELECT @r:= 1) AS r,(SELECT @u:= 0) AS u

ORDER BY User_ID,ID

) AS Photos

B)

我的第一个解决方案是将Order添加到添加行号的排序中,因此任何带有Order的东西首先按其顺序排序,但这只适用于您的订购系统没有间隙且从1开始的情况：

SELECT ID,RowNumber AS `Order`

FROM ( SELECT @r:= IF(@u = User_ID,1) AS `RowNumber`,(SELECT @i:= 1) AS r,`Order`,ID

) AS Photos

ORDER BY `User_ID`,`Order`

订购GAPS

我终于找到了一种维持排序顺序的方法,即使序列中存在间隙也是如此.

SELECT ID,`Order`

FROM Photos

WHERE `Order` IS NOT NULL

UNION ALL

SELECT Photos.ID,Photos.user_ID,Numbers.RowNum

FROM ( SELECT ID,@