问题信:
我有一个称为“客户”的超类表和两个从客户那里继承的名为“人”和“公司”的子表。因此,客户实体与“人”或“公司”具有一对一关系。
(客户只能是“人”或“公司”的一员,而不能同时属于两者)
如下所示:
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,并同时加入“个人”和“公司”以检索名称属性。
以下是我尝试过的方法:
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给出以下结果,而右边是我要实现的目标:
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运算符的方法。
无论如何,我可以执行JOIN,分组/合并名称列吗?还是类似的东西?但是不必使用SQL UNION吗?
更新:
Juan Carlos Oropeza和Becuzz的两个答案同样可以接受。
只需包含一个案例即可选择要使用的数据
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"
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句