|
有桌子照片
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
（编辑：湖南网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
