sql-server - SQL View : Join tables without causing the data to duplicate on every row?

Question

每当我创建 3 个表的 View 时，它都会复制所有行上的所有数据。我不知道如何加入这些，以便它显示空值而不是重复项。你能给我指出正确的方向吗？

这是场景:

• 表 1 = 帐户:帐户名称和帐户 ID
• 表 2 = 属性:属性描述、属性地址和帐户 ID
• 表 3 = 车辆:车辆品牌、车辆型号和帐户 ID

数据看起来像这样:

[Table 1= Accounts]
   id name  accountid

   1 Family A  account001
   2 Family B  account002
   3 Family C  account003



[Table 2= Properties]
  id accountid  description address

  1 account001 home california
  2 account001  beach mexico
  3 account002  hideout arizona
  4 account002  getaway nevada
  5 account002  skilodge idaho
  6 account 003  home texas


[Table 3= Vehicles]

 id description make model accountid
  1 green  Acura Integra  account001
  2 blue  Aston Martin Vantage account001
  3 silver  Audi Quattro  account001
  4 work  Bently Continental GTC account002
  5 kids  Ford Fusion Hybrid account002
  6 Mom's Car  Land Rover LR4 account003
  7 Paper Weight Mini Clubman account003
  8 Beater  Dodge Caliber  account003
  9 Why Mahindra TR40 account003
  10 Kids  Scion xB  account003

我的愿望是创建一个 View 来显示数据库中的所有记录。对于每一行，我想显示帐户名称，然后显示表中的数据。

我正在寻找返回如下结果的 View :如果该数据不是当前查询的列，则它根本不会在该行上显示数据。

account_Name | property_DESCRIPTION | property_ADDRESS | vehicles_DESCRIPTION   vehicles_MAKE | vehicles_MODEL

- Family A  home    california  **null  null    null**
- Family A  beach   mexico  **null  null    null**
- Family A  **null  null**  blue    Aston Martin    Vantage
- Family A  **null  null**  silver  Audi    Quattro
- Family B  hideout arizona **null  null    null**
- Family B  getaway nevada  **null  null    null**
- Family B  skilodge    idaho   **null  null    null**
- Family B  **null  null**  kids    Ford    Fusion Hybrid

但是，它会显示重复的数据，如下所示，每次找到新记录时，它都会使用其他表中的数据填充该行。

account_Name    property_DESCRIPTION    property_ADDRESS    vehicles_DESCRIPTION    vehicles_MAKE   vehicles_MODEL

- Family A  home    california  green   Acura   Integra
- Family A  beach   mexico  green   Acura   Integra
- Family A  home    california  blue    Aston Martin    Vantage
- Family A  beach   mexico  blue    Aston Martin    Vantage
- Family A  home    california  silver  Audi    Quattro
- Family A  beach   mexico  silver  Audi    Quattro
- Family B  hideout arizona work    Bently  Continental GTC
- Family B  getaway nevada  work    Bently  Continental GTC
- Family B  skilodge    idaho   work    Bently  Continental GTC
- Family B  hideout arizona kids    Ford    Fusion Hybrid
- Family B  getaway nevada  kids    Ford    Fusion Hybrid
- Family B  skilodge    idaho   kids    Ford    Fusion Hybrid

为什么会发生这种情况对我来说似乎很有意义；一切都在连接中。就好像连接感觉他们确实需要显示数据，无论成本如何。从脚本编写的角度来看，我似乎需要单独循环表，然后将结果循环连接在一起，而不是连接。

本质上，我需要 View 首先显示表 1 中的所有记录，然后是表 2 中的所有记录，然后是表 3 中的所有记录。我真的不想将这些数据连接在一起；我只想在一个 View 中看到所有内容。

任何人都可以告诉我如何生成上面显示空值而不是重复项的所需 View 吗？

这是在 SQL 2008 R2 Enterprise 上。

Answer 1

在这种情况下，您需要使用 UNION(实际上是 UNION ALL)。

select a.name as account_Name, 
       p.description as property_DESCRIPTION, 
       p.address as property_ADDRESS, 
       null as vehicles_DESCRIPTION,
       null as vehicles_MAKE, 
       null as vehicles_MODEL
    from Accounts a
        inner join Properties p
            on a.accountid = p.accountid
UNION ALL   
select a.name as account_Name, 
       null as property_DESCRIPTION, 
       null as property_ADDRESS, 
       v.description as vehicles_DESCRIPTION,
       v.make as vehicles_MAKE, 
       v.model as vehicles_MODEL
    from Accounts a
        inner join vehicles v
            on a.accountid = v.accountid