什么是写一条将type = 1
用行的总和更新所有sql的正确方法,其中parent =带id的行的ID type=1
。
简而言之:update likesd set totals =所有总计之和,其中parent = type = 1的行的id
"id" "type" "parent" "country" "totals"
"3" "1" "1" "US" "6"
"4" "2" "3" "US" "6"
"5" "3" "3" "US" "5"
所需结果
"id" "type" "parent" "country" "totals"
"3" "1" "1" "US" "17" ->6+6+5=17
"4" "2" "3" "US" "6"
"5" "3" "3" "US" "5"
我正在尝试(但失败了)
UPDATE likesd a
INNER JOIN (
SELECT parent, sum(totals) totalsNew
FROM likesd
WHERE b.parent = a.id
GROUP BY parent
) b ON a.id = b.parent
SET a.totals = b.totalsNew;
您可以使用《 MySQL参考手册》中所述的多表语法来做到这一点:
update likesd a, (select parent, sum(totals) as tsum
from likesd group by parent) b
set a.totals = a.totals + b.tsum
where a.type = 1 and b.parent = a.id;
该查询将更新一行并导致:
+------+------+--------+---------+--------+
| id | type | parent | country | totals |
+------+------+--------+---------+--------+
| 3 | 1 | 1 | US | 17 |
| 4 | 2 | 3 | US | 6 |
| 5 | 3 | 3 | US | 5 |
+------+------+--------+---------+--------+
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句