在 SQL Server 中水平连接两个以上的表 [英] Concatenate more than two tables horizontally in SQL Server

问题描述

以下是架构

+---------+---------+
| Employee Table    |
+---------+---------+
| EmpId   | Name    | 
+---------+---------+
| 1       | John    |
| 2       | Lisa    |
| 3       | Mike    |
|         |         |
+---------+---------+

+---------+-----------------+
| Family   Table            |
+---------+-----------------+
| EmpId   | Relationship    | 
+---------+-----------------+
| 1       | Father          |
| 1       | Mother          |
| 1       | Wife            |
| 2       | Husband         |
| 2       | Child           |
+---------+-----------------+

+---------+---------+
| Loan  Table       |
+---------+--------+
| LoanId  | EmpId  | 
+---------+--------+
| L1      | 1      |
| L2      | 1      |
| L3      | 2      |
| L4      | 2      |
| L5      | 3      |
+---------+--------+

• Employee Table 和 Family Table 是一对多的关系
• Employee Table 和 Loan Table 有很多关系

我尝试过连接，但它给出了多余的行.

I have tried Joins but it gives redundant rows.

现在所需的输出将是

+---------+---------+--------------+---------+
| EmpId   | Name    | RelationShip | Loan    | 
+---------+---------+--------------+---------+
| 1       | John    | Father       | L1      |
| -       | -       | Mother       | L2      |
| -       | -       | Wife         | -       |
| 2       | Lisa    | Husband      | L3      |
| -       | -       | Child        | L4      |
| 3       | Mike    | -            | L5      |
|         |         |              |         |
+---------+---------+--------------+---------+    

推荐答案

您似乎正在尝试将贷款按顺序"分配给族表中的行.解决这个问题的方法是先获取正确的行，然后获取分配给行的贷款.

It looks like you are trying to assign the loans "sequentially" to rows in the family table. The approach to solve this is to first get the right rows, and then to get the loans assigned to rows.

正确的行(和前三列)是:

The right rows (and first three columns) are:

select f.EmpId, e.Name, f.Relationship
from family f join
     Employee e
     on f.empid = e.empid;

请注意，这不会在重复值的列中放置连字符，而是在实际值中放置.尽管您可以在 SQL 中安排连字符，但这是一个坏主意.SQL 结果以表格的形式存在，表格是无序集合，每列和每行都有值.当您开始输入连字符时，您取决于顺序.

Note that this does not put hyphens in the columns for repeated values, it puts in the actual values. Although you can arrange for the hyphens in SQL, it is a bad idea. SQL results are in the form of tables, which are unordered sets with values for each column and each row. When you start putting hyphens in, you are depending on the order.

现在的问题是加入贷款.这实际上很简单，通过使用 row_number() 添加一个 join 键:

Now the problem is joining in the loans. This is actually pretty easy, by using row_number() to add a join key:

select f.EmpId, e.Name, f.Relationship, l.LoanId
from Employee e left join
     (select f.*, row_number() over (partition by f.EmpId order by (select NULL)) as seqnum
      from family f
     ) f 
     on f.empid = e.empid left join
     (select l.*, row_number() over (partition by l.EmpId order by (select NULL)) as seqnum
      from Loan l
     ) l
     on f.EmpId = l.EmpId and f.seqnum = l.seqnum;

请注意，这并不能保证给定员工的贷款分配顺序.您的数据似乎没有足够的信息来处理更一致的分配.

Note that this does not guarantee the order of assignment of loans for a given employee. Your data does not seem to have enough information to handle a more consistent assignment.

这篇关于在 SQL Server 中水平连接两个以上的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！