代码之家  ›  专栏  ›  技术社区  ›  Stefan Collier

SQL Server-合并时合并重复键上的数据

  •  1
  • Stefan Collier  · 技术社区  · 6 年前

    我试过了 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);
    
    1 回复  |  直到 6 年前
        1
  •  1
  •   Lukasz Szozda    6 年前

    你可以用 GROUP BY 要展平源:

    WITH source AS (
        SELECT
          u.name,
          high_score = MIN(h.high_score),
          colour = MIN(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
        GROUP BY u.name
    )
    MERGE INTO user_brief AS target
    USING 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);