Geek asks and answers

A query to add an incremental field based on GROUP BY

You have a table of photos

photos.id
photos.user_id
photos.order

A) Is it possible with a single request to group all the photos by the user, and then update the order 1,2,3..N?

B) a twist is added, but what if some of the photos already have an order value? Make sure the new photos.order file never repeats and fills ant orders lower or higher than the existing ones (as best as possible)

My only thought is to just run the script on this and skip it and reorder everything?

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

Now the data may look like this:

user_id = 1
photo_id = 1
order = NULL

user_id = 2
photo_id = 2
order = NULL

user_id = 1
photo_id = 3
order = NULL

the desired result will be

user_id = 1
photo_id = 1
order = 1

user_id = 2
photo_id = 2
order = 1

user_id = 1
photo_id = 3
order = 2
+5
source share
1 answer

AND)

You can use a variable that increments with each row and is reset with each user_ID to get the number of rows.

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

SQL

)

, Order , , Order , , 1:

SELECT  ID,
        User_ID,
        RowNumber AS `Order`
FROM    (   SELECT  @r:= IF(@u = User_ID, @r + 1,1) AS `RowNumber`,
                    ID,
                    User_ID,
                    @u:= User_ID
            FROM    Photos,
                    (SELECT @i:= 1) AS r,
                    (SELECT @u:= 0) AS u
            ORDER BY User_ID, `Order`, ID
        ) AS Photos
ORDER BY `User_ID`, `Order`

Order

GAPS

, .

SELECT  ID, User_ID, `Order`
FROM    Photos
WHERE   `Order` IS NOT NULL
UNION ALL
SELECT  Photos.ID,
        Photos.user_ID,
        Numbers.RowNum
FROM    (   SELECT  ID,
                    User_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`

, , . , Order , . Order . - 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 , Order, RowNum, User_ID,

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

RowNum2 rownum, , Order. , .

SQL

+11

More articles:


All Articles