mysql - 这个sql中的where子句会放在哪里？

Question

SQL fiddle here

"id"    "type"  "parent"    "country"   "totals"
1       3       0           US          0       //Desired output 5
2       10      1           US          0
3       10      1           US          0
4       10      1           US          0
5       10      1           US          0
6       10      1           US          0

7       3       0           US          0       //Desired output 5
8       10      7           US          0
9       10      7           US          0
10      10      7           US          0
11      10      7           US          0
12      10      7           US          0

13      3       0           SE          0       //Desired output 1
14      10      13          SE          0

15      3       0           SE          0       //Desired output 3
16      10      15          SE          0
17      10      15          SE          0
18      10      15          SE          0

在上表中，我尝试用其子项的计数更新父项 (how many children a parent has. I'll supply the parent id and country) .

parent 是type 3 children 是type 10国家/地区代码位于侧面。

我想做的是:

$parent = 10;
$country = 'US';

`select count(*) as totalChildren, parent, country where type= 10 and parent = $parent and country = $country` then
`update table set totals = totalChildren where parent = parent from above and country = country from above`.

我目前使用下面的 SQL 来更新整个表，但是如果我只需要更新特定的父表，我应该将 where 子句放在下面的 sql 中。我对此有点困惑。

UPDATE  likesd a // where parent = $parent and country = $country - where should this go?
        INNER JOIN
        (
            SELECT  country, parent, count(id) totalChildren
            FROM    likesd
            WHERE   type = 10
            GROUP   BY parent
        ) b ON a.id=b.parent and a.country = b.country
SET     a.totals = b.totalChildren
WHERE   a.type = 3 and a.id = b.parent and a.country = b.country;

Answer 1

由于您只有一个 child 与 parent 的关系，因此这应该可行:

UPDATE likesd l
JOIN (
  SELECT COUNT(1) cnt, parent
  FROM likesd 
  WHERE parent = ?
  GROUP BY parent
  ) l2 ON l.id = l2.parent
SET l.totals = l2.cnt

Update Fiddle Demo

我假设 ID 是您的主键，因此不需要提供国家/地区。如果确实需要，请将其放在子查询的 WHERE 子句中。