我想用表 A A.id=B.id
中的sum(aa)
更新表 B,并进入A.d
设置的列(例如 A:223, d1, 5
更新 B:id 223
,列 d1 = d1 + 5
(。
TABLE A TABLE B
id d aa id d1 d4 d7
--- +-------+---- ----+-------+-------+-----
223 | d1 | 5 221 | 0 | 5 | 0 <
224 | d1 | 5 222 | 0 | 5 | 0 <
225 | d1 | 5 223 | 5 | 0 < | 0 <
226 | d1 | 5 224 | 5 | 0 < | 0 <
| | 225 | 5 | 0 < | 0 <
221 | d4 | 5 226 | 5 | 0 < | 0 <
222 | d4 | 5 < = missing
223 | d4 | 5
224 | d4 | 5
225 | d4 | 5
226 | d4 | 5
| | EXPECTED:
221 | d7 | 5 id d1 d4 d7
222 | d7 | 4 ----+-------+-------+------
223 | d7 | 5 221 | 0 | 5 | 5
224 | d7 | 5 222 | 0 | 5 | 5
224 | d7 | 5 223 | 5 | 5 | 5
225 | d7 | 5 224 | 5 | 5 | 10
226 | d7 | 5 225 | 5 | 5 | 5
226 | d7 | 5 226 | 5 | 5 | 15
226 | d7 | 5
查询:(由于我不能使用动态列名,因此需要CASE WHEN THEN
(
UPDATE `TABLE_B` `B`
JOIN(
SELECT `id`,`d`,SUM(`aa`)`aa`
FROM `TABLE_A` GROUP BY `id`,`d`
) `A`
ON `A`.`id`=`B`.`id`
SET
`d1`= CASE `B`.`d` WHEN "d1" THEN `A`.`d1`+`B`.`aa` ELSE `A`.`d1` END,
`d2`= CASE `B`.`d` WHEN "d2" THEN `A`.`d2`+`B`.`aa` ELSE `A`.`d2` END,
`d3`= CASE `B`.`d` WHEN "d3" THEN `A`.`d3`+`B`.`aa` ELSE `A`.`d3` END,
`d4`= CASE `B`.`d` WHEN "d4" THEN `A`.`d4`+`B`.`aa` ELSE `A`.`d4` END,
`d5`= CASE `B`.`d` WHEN "d5" THEN `A`.`d5`+`B`.`aa` ELSE `A`.`d5` END,
`d6`= CASE `B`.`d` WHEN "d6" THEN `A`.`d6`+`B`.`aa` ELSE `A`.`d6` END,
`d7`= CASE `B`.`d` WHEN "d7" THEN `A`.`d7`+`B`.`aa` ELSE `A`.`d7` END
问题是这只会更新唯一 id。它从d1
中获取223, 224, 225, 226
,然后仅从d4
中获取221, 222
,从d7
中不需要任何值,而不是更新16个值(4x d1,6x d4,6x d7(。
那么,我到底哪里做错了JOIN
呢?
小提琴
我通过使用两个子查询首先创建一个包含所有列的完整表并且每个id
只有一行来获得解决方案:
SELECT
`id`,
CASE `d` WHEN "d1" THEN SUM(`aa`) ELSE 0 END AS `a1`,
CASE `d` WHEN "d4" THEN SUM(`aa`) ELSE 0 END AS `a4`,
CASE `d` WHEN "d7" THEN SUM(`aa`) ELSE 0 END AS `a7`
FROM `TABLE_A`
GROUP BY `id`,`d`
给予(仅 id 的 221 和 226 示例(:
id d1 d4 d7
----+---+---+----
221 | 0 | 5 | 0
221 | 0 | 0 | 5
226 | 5 | 0 | 0
226 | 0 | 5 | 0
226 | 0 | 0 | 15
然后确保每个id
只有一行:
SELECT
`id`,
SUM(`a1`) `a1`,
SUM(`a4`) `a4`,
SUM(`a7`) `a7`
FROM(
.... previous select ...
)
GROUP BY `id`
给予(仅 id 的 221 和 226 示例(:
id d1 d4 d7
----+---+---+----
221 | 0 | 5 | 5
226 | 5 | 5 | 15
现在我可以更新表 B:
UPDATE `TABLE_B` `B`
JOIN(
SELECT
`id`,
SUM(`a1`) `a1`,
SUM(`a4`) `a4`,
SUM(`a7`) `a7`
FROM(
SELECT
`id`,
CASE `d` WHEN "d1" THEN SUM(`aa`) ELSE 0 END AS `a1`,
CASE `d` WHEN "d4" THEN SUM(`aa`) ELSE 0 END AS `a4`,
CASE `d` WHEN "d7" THEN SUM(`aa`) ELSE 0 END AS `a7`
FROM `TABLE_A`
GROUP BY `id`,`d`
) `C`
GROUP BY `id`
)`A`
ON `A`.`id`=`B`.`id`
SET
`d1`=`d1`+`a1`,
`d4`=`d4`+`a4`,
`d7`=`d7`+`a7`
也许有更好的解决方案,但这有效。