mysql - 根据 parent 和国家/地区的总 child 数 count() 更新 parent

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

在上表中，我尝试用其 child 的数量来更新所有 parent (每个 child 有多少个 child )。

parent 是类型3， child 是类型10，国家在旁边。

我想做的是:

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

我一直在这方面做一些事情，但似乎无处可去。你能帮我吗？

UPDATE  likesd a
        INNER JOIN
        (
            SELECT  country, parent, count(id) totalChildren
            FROM    likesd
            WHERE   type = 10
            GROUP   BY parent
        ) b ON a.country = b.country and a.parent=b.parent
SET     a.totals = b.totalChildren
WHERE   a.type = 3 and a.country = b.country;

编辑 - 工作答案

UPDATE  likesd a
        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

这应该有效。您有 a.parent = b.parent，它应该是 a.id = b.parent。

UPDATE  likesd a
    INNER JOIN
    (SELECT  parent, count(id) totalChildren
       FROM    likesd
      WHERE   type = 10
      GROUP   BY parent) b ON a.id=b.parent
SET     a.totals = b.totalChildren
WHERE   a.type = 3

(很抱歉格式奇怪。)

这里的 SQL fiddle :http://sqlfiddle.com/#!2/0c5b0d/1