时间戳

首页 > 解决方案 > 寻找一种特殊的方式加入 SQL 服务器

sql-server - 寻找一种特殊的方式加入 SQL 服务器

问题描述

我想加入两个表，但找不到任何现有的联接（我尝试了左、右、全、交叉）来做到这一点。我想将表 1 和表 2 合并到表 3 中。数据顺序是按时间顺序排列的，我希望在所需的表中看到相同的顺序。

表格1：

学生---- Score1
A------------ 90
A------------ 80
B------------ 85
B- ----------- 60
C------------ 50
C------------ 40

表2：

学生---- Score2
A------------ 66
A------------ 70
A------------ 85
B- ----------- 60
C------------ 40

表 3：所需表

学生---- Score1-----Score2
A------------ 90 ----------- 66
A----------- - 80 ----------- 70
A------------空 -----------85
B--------- --- 85 ----------- 60
B------------ 60 ----------- 空
C------- ----- 50 ------------ 40
C------------ 40 ----------- 空

谢谢！

标签： sql-serverjoin

解决方案

好的，我们首先需要尝试找到一种在运行时向表中添加位置列的方法。这可以通过 ROW_NUMBER() 函数来完成：

SELECT *, ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Student) Position FROM Table1

SELECT *, ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Student) Position FROM Table2

这会在我们的结果中创建一个不错的 Position 列：

Student    Score1      Position
---------- ----------- --------------------
A          90          1
A          80          2
B          85          1
B          60          2
C          50          1
C          40          2

(6 rows affected)

Student    Score2      Position
---------- ----------- --------------------
A          66          1
A          70          2
A          85          3
B          60          1
C          40          1

(5 rows affected)

现在我们需要加入这两个临时结果。由于您想包含每个表中的所有行，因此将不匹配行的空格留空（NULL）。FULL OUTER JOIN来拯救，在它所有的美丽中：

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Student) Position FROM Table1) T1
FULL OUTER JOIN
(SELECT *, ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Student) Position FROM Table2) T2 
    ON T1.Student = T2.Student AND T1.Position = T2.Position

我们得到这个：

Student    Score1      Position             Student    Score2      Position
---------- ----------- -------------------- ---------- ----------- --------------------
A          90          1                    A          66          1
A          80          2                    A          70          2
NULL       NULL        NULL                 A          85          3
B          85          1                    B          60          1
B          60          2                    NULL       NULL        NULL
C          50          1                    C          40          1
C          40          2                    NULL       NULL        NULL

(7 rows affected)

现在只需选择您感兴趣的内容：

SELECT COALESCE(T1.student, T2.student) Student,
       T1.score1,
       T2.score2
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Student) Position FROM Table1) T1
FULL OUTER JOIN
(SELECT *, ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Student) Position FROM Table2) T2 
    ON T1.Student = T2.Student AND T1.Position = T2.Position

瞧：

Student    score1      score2
---------- ----------- -----------
A          90          66
A          80          70
A          NULL        85
B          85          60
B          60          NULL
C          50          40
C          40          NULL

(7 rows affected)

但请注意：对于许多记录，这可能不是存储和检索数据的最有效方式......

编辑：接受答案后已添加以下内容

真的很重要：既然评论中产生了一个小的谩骂，让我们说清楚。

OP提出的数据库设计有很多缺陷，首先它是基于假设表中记录的顺序总是插入记录的顺序。

这不可能是真的，并且我的解决方案无法按预期工作，直到实施了一些更强大的记录排序方式。

最好在CreatedAt两个表中添加一个类型为 的列datetime来存储记录插入日期：

ALTER TABLE dbo.Table1 ADD
    CreatedAt datetime NOT NULL CONSTRAINT DF_Table1_CreatedAt DEFAULT getdate()

ALTER TABLE dbo.Table2 ADD
    CreatedAt datetime NOT NULL CONSTRAINT DF_Table2_CreatedAt DEFAULT getdate()

这可以允许更安全地订购记录。

解决方案将更改如下：

SELECT COALESCE(T1.student, T2.student) Student,
       T1.score1,
       T2.score2
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Student ORDER BY CreatedAt) Position FROM Table1) T1
FULL OUTER JOIN
(SELECT *, ROW_NUMBER() OVER (PARTITION BY Student ORDER BY CreatedAt) Position FROM Table2) T2 
    ON T1.Student = T2.Student AND T1.Position = T2.Position

推荐阅读