我试过了
MERGE
但我有个关键错误。
MySQL中的等效查询(我无法转换)是:
请注意,我已经更改了所有字段和表名
INSERT INTO user_brief (name, high_score, colour)
SELECT
u.name,
h.high_score,
p.colour,
FROM foo_table AS f
LEFT JOIN users AS u ON f.user_id = u.id
LEFT JOIN high_scores AS h ON f.user_id = h.id
LEFT JOIN preferences AS p ON f.user_id = p.id
ON DUPLICATE KEY
UPDATE
name = COALESCE(user_brief.name, VALUES(name)),
high_score = COALESCE(user_brief.high_score, VALUES(high_score)),
colour = COALESCE(user_brief.colour, VALUES(colour));
选择查询结果
如果我们只进行选择,您将得到以下结果:
name | high_score | color
---------------------------
foo | NULL | brown
foo | 40 | NULL
bar | 29 | blue
...
期望的结果
name | high_score | color
foo | 40 | brown
bar | 29 | blue
...
正如您所看到的,它已经扁平化(不确定这是否是正确的术语)为一个数组的每一列获取第一个非空值
name
键入的记录。
我的尝试
合并
解决方案(但会出现关键错误):
MERGE INTO user_brief AS target
USING (SELECT
u.name,
h.high_score,
p.colour,
FROM foo_table AS f
LEFT JOIN users AS u ON f.user_id = u.id
LEFT JOIN high_scores AS h ON f.user_id = h.id
LEFT JOIN preferences AS p ON f.user_id = p.id) AS source
ON target.name = source.name
WHEN MATCHED THEN
UPDATE SET
target.name = COALESCE(source.name, target.name),
target.high_score = COALESCE(source.high_score, target.high_score),
target.colour = COALESCE(source.colour, target.colour)
WHEN NOT MATCHED BY TARGET THEN
INSERT (name, high_score, colour)
VALUES (source.name, source.high_score, source.colour);