php - 根据 id 与条件匹配更新一个 MySQL 表值

Question

我有下面这两张表:用户:

         id 7dexpn
=========== ==========
          1 0
          2 0
          3 0


user_pages:
        id     user_id 7dexpf
========== =========== ==========
       99           1 0
       98           2 1
       97           3 1
       96           3 1
       95           3 1
       94           2 0

我已成功将 { user_pages (7dexpf) flags 聚合}插入用户表 (7dexpn) 中，通过 user_pages (user_id) 与用户表 (id) 进行匹配使用此查询

update users u join
       (select user_id, count(*) as cnt
        from user_pages 
        where `7dexpf` = 1
        group by user_id
       ) uu
       on uu.user_id = u.id
    set u.`7dexpn` = uu.cnt;

但是如果 u.7dexpn = 0，则查询不会将标志更新回零

Answer 1

使用 LEFT JOIN 和 COALESCE() :

UPDATE users u 
LEFT JOIN
     (SELECT user_id, COUNT(*) as cnt
      FROM user_pages 
      WHERE `7dexpf` = 1
      GROUP BY user_id
     ) uu
 ON uu.user_id = u.id
SET u.`7dexpn` = COALESCE(uu.cnt,0);