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

有桌子照片

photos.id
photos.user_id
photos.order

A)是否可以通过单个查询按用户对所有照片进行分组,然后更新订单1,2,3..N？

B)添加了扭曲,如果某些照片已经关联了订单价值怎么办？确保新的photos.order永远不会重复,并填写低于或高于现有的蚂蚁订单(尽可能最好)

我唯一的想法就是在这个上运行一个脚本并循环遍历并重新排序所有内容？

photos.id int(10)
photos.created_at datetime
photos.order int(10)
photos.user_id int(10)

现在数据可能看起来像这样

user_id = 1
photo_id = 1
order = NULL

user_id = 2
photo_id = 2
order = NULL

user_id = 1
photo_id = 3
order = NULL

期望的结果将是

user_id = 1
photo_id = 1
order = 1

user_id = 2
photo_id = 2
order = 1

user_id = 1
photo_id = 3
order = 2

最佳答案
一个)

您可以使用随每行递增的变量,并使用每个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

Example on SQL Fiddle

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`

Example using Order Field

订购GAPS

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

SELECT  ID,`Order`
FROM    Photos
WHERE   `Order` IS NOT NULL
UNION ALL
SELECT  Photos.ID,Photos.user_ID,Numbers.RowNum
FROM    (   SELECT  ID,@r1:= IF(@u1 = User_ID,@r1 + 1,1) AS RowNum,@u1:= User_ID 
            FROM    Photos,(SELECT @r1:= 0) AS r,(SELECT @u1:= 0) AS u
            WHERE   `Order` IS NULL
            ORDER BY User_ID,ID
        ) AS Photos
        INNER JOIN
        (   SELECT  User_ID,RowNum,@r2:= IF(@u2 = User_ID,@r2 + 1,1) AS RowNum2,@u2:= User_ID 
            FROM    (   SELECT  DISTINCT p.User_ID,o.RowNum
                        FROM    Photos AS p,(   SELECT  @i:= @i + 1 AS RowNum
                                    FROM    INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY,( SELECT @i:= 0) AS i
                                ) AS o
                        WHERE   RowNum <= (SELECT COUNT(*) FROM Photos P1 WHERE p.User_ID = p1.User_ID)
                        AND     NOT EXISTS
                                (   SELECT  1
                                    FROM    Photos p2
                                    WHERE   p.User_ID = p2.User_ID
                                    AND     o.RowNum = p2.`Order`
                                )
                        AND     p.`Order` IS NULL
                        ORDER BY User_ID,RowNum
                    ) AS p,(SELECT @r2:= 0) AS r,(SELECT @u2:= 0) AS u
            ORDER BY user_ID,RowNum
        ) AS numbers
            ON Photos.User_ID = numbers.User_ID
            AND photos.RowNum = numbers.RowNum2
ORDER BY User_ID,`Order`

但是你可以看到这很复杂.这通过将具有订单值的那些单独处理为没有订单值的那些来工作顶部查询只按照每个用户的ID顺序对没有订单值的所有照片进行排名.底部查询使用交叉连接为每个用户ID生成从1到n的顺序列表(最多为每个User_ID的条目数).所以使用这样的数据集：

ID  User_ID Order
1   1       NULL
2   2       NULL
3   1       NULL
4   1       1
5   1       3
6   2       2
7   2       3

它会产生

UserID  RowNum
1       1
1       2
1       3
1       4
2       1
2       2
2       3

然后,它使用NOT EXISTS来消除Photos已使用非空订单的所有组合,并按User_ID分配的RowNum顺序排列

UserID  RowNum  Rownum2
1       2       1
1       4       2
2       1       1

然后可以将RowNum2值与from子查询中获得的rownum值匹配,从而给出正确的顺序值.啰嗦,但它确实有效.

Example on SQL Fiddle