mysql - 更新查询不更新几行

Question

用下表:

"id"    "type"  "parent"    "country"   "votes" "perCent"
"1"     "1"     "0"         "US"        "0"     "0"//Appears as 0, should be 12
"2"     "2"     "1"         "US"        "0"     "0"//Appears as 0, should be 8
"3"     "3"     "2"         "US"        "4"     "0"
"7"     "3"     "2"         "US"        "4"     "0"
"19"    "3"     "1"         "US"        "4"     "0"
"4183"  "10"    "3"         "US"        "2"     "0"
"4184"  "10"    "3"         "US"        "2"     "0"
"4185"  "10"    "7"         "US"        "2"     "0"
"4186"  "10"    "7"         "US"        "2"     "0"
"4187"  "10"    "19"        "US"        "2"     "0"
"4188"  "10"    "19"        "US"        "2"     "0"

为什么当我运行以下查询来更新总计时，行 ID 1 和行 ID 2 显示为 0？

UPDATE  likesd a
        INNER JOIN
        (
            SELECT  parent, country, SUM(votes) totalVotes
            FROM    likesd
            GROUP   BY parent
        ) b ON a.id = b.parent and a.country = b.country
SET     a.votes = b.totalVotes; select * from likesd;

当我这样做时:

SELECT  parent, country, SUM(votes) totalVotes
            FROM    likesd
            GROUP   BY parent;

我可以看到结果中的所有行，但前两行没有更新。

我的 sql 有问题吗？我该如何正确设置？另外，您是否发现我使用的 sql 有任何问题？

Answer 1

你应该把国家也放在 group by 子句中，因为你在更新时也检查国家。

UPDATE  likesd a
        INNER JOIN
        (
            SELECT  parent, country, SUM(votes) totalVotes
            FROM    likesd
            GROUP   BY parent, country
        ) b ON a.id = b.parent and a.country = b.country
SET     a.votes = b.totalVotes; select * from likesd;

编辑如果出现意外结果，您可以尝试遵循更新语句 -

UPDATE Likesd a
   SET Voltes = (SELECT SUM(Votes)
                   FROM Likesd b
                  WHERE b.PARENT = a.Id
                    AND b.Country = a.Country)