TSQL合并2数据集，相邻有偶数行

Question

我想要做的是：

    Dataset 1
    Name1
    Name2
    Name3

    Dataset 2
    Number1
    Number2
    Number3

将成为两栏：

dataset1   dataset2
Name1      Number1
Name2      Number2
Name3      Number3

我的数据集1和2将始终具有相等的行。哪个名字链接到哪个数字，我不关心，只要两个名字没有链接到同一个数字，反之亦然。

如何使用SQL / Server解决这一问题？

Answer 1

如果不想向表中添加标识列，可以使用ROW_NUMBER()函数如下：

SELECT
    T1.Col1,
    T2.Col1
FROM
    (SELECT Col1, ROW_NUMBER() OVER (ORDER BY Col1) AS N FROM Table1) T1
INNER JOIN
    (SELECT Col1, ROW_NUMBER() OVER (ORDER BY Col1) AS N FROM Table2) T2
ON T1.N = T2.N

在这里，将Table1和Table2替换为表的名称，并将Col1替换为要从两个表输出的列(或多个列)的名称。

Answer 2

向两个表中添加标识列，并根据这些列执行联接

ALTER TABLE Table1
  ADD ID INT IDENTITY(1,1) NOT NULL


ALTER TABLE Table2
  ADD ID INT IDENTITY(1,1) NOT NULL


SELECT Table1.dataset1col , Table2.dataset2Col
From Table1 INNER JOIN Table2 
     ON Table1.ID = Table2.ID

Answer 3

这可能对你有用：

;WITH cte1 (name, rn)
     AS (SELECT Name,
                row_number()
                  OVER(
                    ORDER BY Name) rn
         FROM   Dataset1),
     cte2 (Number, rn)
     AS (SELECT Number,
                row_number()
                  OVER(
                    ORDER BY Number) rn
         FROM   Dataset2)
SELECT name,
       Number
FROM   cte1
       JOIN cte2
         ON cte1.rn = cte2.rn