sql - 连接宽表(10列唯一列)

我有多个需要在多个公共属性上连接的表，这样不同的属性可以显示在一个表中。
表1

+--------+---------+-------+
|  make  |  model  | r_yr  |
+--------+---------+-------+
| toyota | corolla |  1999 |
| toyota |  camry  |  2002 |
| toyota |  qualis |  2004 |
| toyota |  rav4   |  2006 |
+--------+---------+-------+

表2

+--------+---------+--------+
|  make  |  model  |  kms   |
+--------+---------+--------+
| toyota | corolla |  25000 |
| toyota |  camry  |  50000 |
+--------+---------+--------+

表4

+--------+---------+---------+
|  make  |  model  | mileage |
+--------+---------+---------+
| toyota | corolla |      20 |
| toyota |  qualis |      25 |
+--------+---------+---------+

表5

+--------+----------+-------+
|  make  |  model   | colr  |
+--------+----------+-------+
| toyota |  camry   | blue  |
| toyota |  rav4    | green |
+--------+----------+-------+

我正在做以下的工作来加入结果

select a.make, a.model,a.r_yr,b.kms,c.mileage,d.colr
    from table1 as a
    left join table2 as b
    on b.make=a.make and b.model=a.model and b.r_yr=a.r_yr
    left join table3 as c
    on c.make=a.make and c.model=a.model and c.r_yr=a.r_yr
    left join table4 as d
    on d.make=a.make and d.model=a.model and d.r_yr=a.r_yr

这张桌子如下

+--------+---------+-------+-------+----------+--------+
|  make  |  model  | r_yr  |  kms  |  mileage |  colr  |
+--------+---------+-------+-------+----------+--------+
| toyota | corolla |  1999 | 25000 |       20 |        |
| toyota |  camry  |  2002 | 50000 |          |  blue  |
| toyota |  qualis |  2004 |       |       25 |        |
| toyota |  rav4   |  2006 |       |          | green  |
+--------+---------+-------+-------+----------+--------+

然而，我遇到的问题是，对于我正在使用的实际数据集，每个表有5个common cols和大约20-40个unique attributes需要以b.kms, ....,c.mileage, ......,d.colr,....的形式在查询中指定20-40个列名称。是否可以通过指定除common cols以外的所有列或其他方式来避免指定这些唯一列？

最佳答案

你不能做像SELECT all except x,y,z ...
但是您可以使用USING子句而不是JOIN ... ON来简化此查询
演示：http://sqlfiddle.com/#!17/fa97a/6

select *
from table1 as a
left join table2 as b
USING (make, model)
left join table3 as c
USING (make, model)
left join table4 as d
USING (make, model)

|   make |   model | r_yr |    kms | mileage |   colr |
|--------|---------|------|--------|---------|--------|
| toyota |   camry | 2002 |  50000 |  (null) |   blue |
| toyota | corolla | 1999 |  25000 |      20 | (null) |
| toyota |  qualis | 2004 | (null) |      25 | (null) |
| toyota |    rav4 | 2006 | (null) |  (null) |  green |

注意：在上面的示例中，我只使用两个公共列(make, model)，因为在您的示例中r_yr不是公共列，因为它只在表1中

关于sql - 连接宽表(10列唯一列)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47959468/