在SQL中使用合并操作将两个表合并

Question

我有一个问题，我正在尝试将来自一张表的数据合并和整合到另一张表中。

源表和目标表完全相同，除了目标表需要在第一列上设置主键。

输入的数据和期望的结果如下：

data in        Desired results   
 c1 c2 c3       c1   c2    c3  
+--+--+--+    +--+------+------+  
 1  A  x       1  A B C  x y  
 2  B  z       2  B C    z  
 3  A  z       3  A      z x y   
 1  A  y      +--+------+------+  
 3  A  y   
 1  B  x     
 2  C  z     
 1  C  x     
 3  A  x   
 1  A  x   
+--+--+--+ 

我初始化了表格...

CREATE TABLE s (c1 char(2), c2 char(8), C3 char(8))

INSERT INTO s VALUES 
  ('1','A','x'), ('2','B','z'), ('3','A','z'), 
  ('1','A','y'), ('3','A','y'), ('1','B','x'), 
  ('2','C','z'), ('1','C','z'), ('3','A','x'), 
  ('1','A','x')

CREATE TABLE d (c1 char(2) PRIMARY KEY, c2 char(8), C3 char(8))

我可以使用光标使它工作...

DECLARE @c1 Char(2)
DECLARE @c2 char(8)
DECLARE @C3 char(8)

DECLARE cur CURSOR FOR SELECT c1, c2, C3 FROM s
OPEN cur
FETCH NEXT FROM cur INTO @c1, @c2, @C3
WHILE @@FETCH_STATUS = 0
BEGIN 
  UPDATE d SET 
    c2 = (CASE WHEN CHARINDEX(RTRIM(@c2), c2) > 0 THEN c2 ELSE SUBSTRING(RTRIM(c2) + ' ' + @c2, 1, 8) END),
    c3 = (CASE WHEN CHARINDEX(RTRIM(@c3), c3) > 0 THEN c3 ELSE SUBSTRING(RTRIM(c3) + ' ' + @c3, 1, 8) END)
    WHERE c1 = @c1
  IF @@ROWCOUNT = 0
  BEGIN
    INSERT INTO d (c1, c2, c3) 
      VALUES (@c1, @c2, @c3)
  END
  FETCH NEXT FROM cur INTO @c1, @c2, @c3
END
CLOSE cur
DEALLOCATE cur

但是我无法让合并工作...

错误：违反了主键约束“PK__d__3213663B03BB8E22”。在对象“dbo.d”中无法插入重复键值(1 )。

MERGE INTO d USING s 
  ON (s.c1 = d.c1)
  WHEN MATCHED THEN 
    UPDATE SET 
      c2 = (CASE WHEN CHARINDEX(RTRIM(s.c2), d.c2) > 0 THEN d.c2 ELSE SUBSTRING(RTRIM(d.c2) + ' ' + s.c2, 1, 8) END),
      c3 = (CASE WHEN CHARINDEX(RTRIM(s.c3), d.c3) > 0 THEN d.c3 ELSE SUBSTRING(RTRIM(d.c3) + ' ' + s.c3, 1, 8) END)
  WHEN NOT MATCHED THEN
    INSERT (c1, c2, c3) VALUES (s.c1, s.c2, s.c3);

有没有比逐行进行更为优雅的方法来完成这个任务？感谢您的帮助！

- Pat Legate

---

这是为哪个关系数据库管理系统（RDBMS）设计的？请添加一个标签来指定您是否正在使用 mysql、postgresql、sql-server、oracle 或 db2，或者完全不同的内容。 - marc_s

@marc_s - 语法明确表明它是 sql server。 - Pரதீப்

Answer 1

您可以使用For XML Path技巧来连接每个组的行，从而避免使用RBAR操作。

;WITH cte1 
     AS (SELECT DISTINCT c1,cs.c2 AS c2 
         FROM   s AS A 
                CROSS apply (SELECT DISTINCT c2 + ' ' 
                             FROM   s AS B 
                             WHERE  A.c1 = B.c1 
                             FOR xml path('')) cs (c2)), 
     cte3 
     AS (SELECT DISTINCT c1,cs.c3 AS c3 
         FROM   s AS A 
                CROSS apply (SELECT DISTINCT c3 + ' ' 
                             FROM   s AS B 
                             WHERE  A.c1 = B.c1 
                             FOR xml path('')) cs (c3)) 
SELECT A.c1, 
       a.c2, 
       b.c3 
FROM   cte1 A 
       INNER JOIN cte3 B 
               ON A.c1 = b.c1 

• SQLFIDDLE DEMO

- Pரதீப்

Answer 2

这最好留给表现层处理，

您可以使用相关子查询来基于c1分组连接c2、c3的值

您需要使用STUFF和for xml path来获取连接后的字符串

select c1, stuff( (select distinct ' ' + c2
               from s
               where s.c1 = outs.c1
               for xml path('')), 1,1,''),
           stuff( (select distinct ' ' + c3
               from s
               where s.c1 = outs.c1
               for xml path('')), 1,1,'')
from s outs
group by outs.c1

- radar