mysql - Mysql 中的嵌套查询更新？

Question

我有以下查询:

SELECT `peers`.uid, `user`.userid FROM `peers`, `user` WHERE `user`.userid = `peers`.uid

FF。是查询的结果

userid,uid
1,1
1,1
1,1
3,3
96,96
96,96

为了更新结果，我设置了 ff 查询:

UPDATE `user`
   INNER JOIN (    
   SELECT `peers`.uid, `user`.userid FROM `peers`, `user` WHERE `user`.userid = `peers`.uid
       ) AS my_current_select ON `user`.userid = my_current_select.userid
   SET `user`.credits = `user`.credits + 0.25

但是update查询只更新了3次，每次加一次0.25 credits into userID 1,3,96

这不是我想要的。我想在查询的每一行结果中唯一添加 0.25 个积分。
例如:

UID 1: UID1.credit = UID1.credit + 0.25 * 3 
UID 3: UID3.credit = UID3.credit + 0.25 * 1 
UID 3: UID96.credit = UID96.credit + 0.25 * 2

我怎样才能达到这个结果？

Answer 1

也许将您的内部查询更改为:

SELECT `user`.userid, COUNT(`peers`.uid) as count 
FROM `peers`, `user` 
WHERE `user`.userid = `peers`.uid 
GROUP BY `user`.userid

得到:

userid,count
1,3
3,1
96,2

然后在您的主查询中执行:

UPDATE `user`
   INNER JOIN (    
   SELECT `user`.userid, COUNT(`peers`.uid) as count 
    FROM `peers`, `user` 
    WHERE `user`.userid = `peers`.uid 
    GROUP BY `user`.userid
       ) AS my_current_select ON `user`.userid = my_current_select.userid
   SET `user`.credits = `user`.credits + 0.25*my_current_select.count