56,677
社区成员
发帖
与我相关
我的任务
分享
UPDATE pass_activity SET overall_sort = (10 - STATUS) * 100000000 + instance_number + (SELECT
n.num FROM (SELECT ( CASE WHEN vip_rank = 1 AND vip_expdate > NOW() THEN 100
WHEN vip_rank = 2 AND vip_expdate > NOW() THEN 200
WHEN vip_rank = 3 AND vip_expdate > NOW() THEN 300 ELSE 0 END)
+ (CASE WHEN ue.identity_verified IS NOT NULL THEN 50 ELSE 0 END)
AS num
FROM accuvally_user u JOIN accuvally_user_extends ue ON u.account = ue.account WHERE u.account = pass_activity.create_by) AS n)
WHERE id IN( 5395653487500, 4394645338900,7393945083100,1343275124100)
UPDATE pass_activity
SET overall_sort = ( 10 - STATUS ) * 100000000 + instance_number
+ ( SELECT ( CASE WHEN vip_rank = 1
AND vip_expdate > NOW() THEN 100
WHEN vip_rank = 2
AND vip_expdate > NOW() THEN 200
WHEN vip_rank = 3
AND vip_expdate > NOW() THEN 300
ELSE 0
END )
+ ( CASE WHEN ue.identity_verified IS NOT NULL THEN 50
ELSE 0
END ) AS num
FROM accuvally_user u
JOIN accuvally_user_extends ue ON u.account = ue.account
WHERE u.account = pass_activity.create_by
)
WHERE id IN ( 5395653487500, 4394645338900, 7393945083100, 1343275124100 );
UPDATE pass_activity a JOIN (SELECT pa.id, ( CASE WHEN vip_rank = 1 AND vip_expdate > NOW() THEN 100
WHEN vip_rank = 2 AND vip_expdate > NOW() THEN 200
WHEN vip_rank = 3 AND vip_expdate > NOW() THEN 300 ELSE 0 END)
+ (CASE WHEN ue.identity_verified IS NOT NULL THEN 50 ELSE 0 END)
AS num
FROM accuvally_user u JOIN accuvally_user_extends ue ON u.account = ue.account JOIN pass_activity pa ON u.account = pa.create_by) AS n
ON a.id = n.id
SET overall_sort = (10 - STATUS) * 100000000 + instance_number + n.num
WHERE a.id IN( 5395653487500, 4394645338900,7393945083100,1343275124100)
刚刚我自己写的,应该是可以的。还有没有其他方式?