如何合并(合并)相似的列以通过JOIN删除NULL

问题描述

问题简介:

我有一个称为"Customers"的超类表和两个从"Customer"和"Company"继承自Customer的子表.因此，客户实体与人"或公司"具有一对一的关系.

I have a superclass table called "Customers" and two child tables that inherit from Customers called "Person" and "Company". So that, Customer entity has a one-to-one relationship with "Person" or "Company".

(客户只能是人"或公司"的客户，而不能同时是两者)

这表示如下:

Customer                    Person                      Company
+-------+------+------+     +-------+------+------+     +-------+------+------+
|    cID|  col2|  col3|     |    cID| fname| sname|     |    cID|  name|  col3|
+-------+------+------+     +-------+------+------+     +-------+------+------+
|1      |?     |?     |     |1      |JJ    |AZ    |     |4      |ABCD  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|2      |?     |?     |     |2      |CC    |LL    |     |5      |BCDE  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|3      |?     |?     |     |3      |OO    |BB    |     |6      |CDEF  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|4      |?     |?     |     |7      |JK    |NN    |     |8      |DEFG  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|5      |?     |?     |     |9      |RR    |LW    |     |...    |EFGH  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|6      |?     |?     |     |10     |GN    |QN    |     |...    |FGHI  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|7      |?     |?     |     |...    |XC    |YU    |     |...    |GHIJ  |?     |
+-------+------+------+     +-------+------+------+     +-------+------+------+
|8      |?     |?     |
+-------+------+------+
|9      |?     |?     |
+-------+------+------+
|10     |?     |?     |
+-------+------+------+
|...    |?     |?     |
+-------+------+------+

• 人员表在单独的名字和姓氏属性中存储客户的全名.
• 公司表完全在一个字段中完全存储公司的名称.

意图和目的尝试:

我想做的是查询数据库，以便我可以从客户"表中选择ID，并同时加入个人"和公司"以检索名称属性.

What I would like to do is query the database so that I can select the ID from Customer table, join to both Person and Company to retrieve the name attribute.

以下是我尝试过的内容:

Below is what I have tried:

SELECT      tc."cust_id",
            CONCAT(tp."forename", ' ', tp."surname") AS "name",
            tcp."name"
FROM        "tbl_customer" AS tc
            LEFT JOIN "tbl_person" AS tp
                ON tc."cust_id" = tp."cust_id"
            LEFT JOIN "tbl_company" AS tcp
                ON tc."cust_id" = tcp."cust_id"

执行上述SQL会得到以下结果，而右边是我要实现的目标:

Executing the above SQL gives the following result, and to the right of is what I am looking to achieve:

Result                      Result
+-------+------+------+     +-------+------+
|    cID|  name|  name|     |    cID|  name|
+-------+------+------+     +-------+------+
|1      |JJAZ  |null  |     |1      |JJAZ  |
+-------+------+------+     +-------+------+
|2      |CCLL  |null  |     |2      |CCLL  |
+-------+------+------+     +-------+------+
|3      |OOBB  |null  |     |3      |OOBB  |
+-------+------+------+     +-------+------+
|4      |null  |ABCD  |     |4      |ABCD  |
+-------+------+------+     +-------+------+
|5      |null  |BCDE  |     |5      |BCDE  |
+-------+------+------+     +-------+------+
|6      |null  |CDEF  |     |6      |CDEF  |
+-------+------+------+     +-------+------+
|7      |JKNN  |null  |     |7      |JKNN  |
+-------+------+------+     +-------+------+
|8      |null  |DEFG  |     |8      |DEFG  |
+-------+------+------+     +-------+------+
|9      |RRLW  |null  |     |9      |RRLW  |
+-------+------+------+     +-------+------+
|10     |GNQN  |null  |     |10     |GNQN  |
+-------+------+------+     +-------+------+
|...    |?     |?     |     |...    |?     |
+-------+------+------+     +-------+------+

说明:

如前所述，我正在尝试在同一列下合并个人"和公司"的名称.两个表上的标准JOIN均不起作用，因为它将返回NULL结果.由于LEFT JOIN的性质，将期望NULL值.可以使用SQL UNION非常简单地解决此问题，并且我知道解决方案，但是我正在寻找通过JOIN替代UNION运算符的方法.

As indicated, I am trying to combine the name of both Person and Company under the same column. Standard JOIN on both tables will not work, as it will return NULL results. Due to the nature of LEFT JOIN, NULL values are to be expected. This can be resolved very simply with SQL UNION and I know the solution to, however I am looking for an alternative to the UNION operator via JOIN.

反正我可以执行JOIN，对名称列进行分组/合并吗?还是类似的东西?但是不必使用SQL UNION吗?

Is there anyway I can perform the JOIN, group/ merge the name columns? Or something similar? But not have to use SQL UNION?

更新:

Juan Carlos Oropeza 和 Becuzz 的答案都是可以接受的.

Both answers from Juan Carlos Oropeza and Becuzz are equally acceptable.

推荐答案

只需包含 案例 以选择要使用的数据

Just include a CASE to select what data use

SELECT      tc."cust_id",
            CASE WHEN tp."forename" IS NULL
                   THEN tcp."name"
                 ELSE  CONCAT(tp."forename", ' ', tp."surname")
            AS "name"
FROM        "tbl_customer" AS tc
            LEFT JOIN "tbl_person" AS tp
                ON tc."cust_id" = tp."cust_id"
            LEFT JOIN "tbl_company" AS tcp
                ON tc."cust_id" = tcp."cust_id"

这篇关于如何合并(合并)相似的列以通过JOIN删除NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！