用子女总数、父母智慧和国家智慧更新父母
用子女总数、父母智慧和国家智慧更新父母
提问于 2013-05-26 09:06:57
SQL 这里
"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 each has)的数量更新所有的父母。
父母是type 3，孩子是type 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;回答 1
Stack Overflow用户
回答已采纳
发布于 2013-05-26 09:10:18
这应该能行。你有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花招：http://sqlfiddle.com/#!2/0c5b0d/1
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16757753
复制相关文章
相似问题
腾讯云开发者
