SQL视图：连接表不会导致数据在每一行重复？ [英] SQL View: Join tables without causing the data to duplicate on every row?

问题描述

每当我创建3个表的视图时，它将复制所有行上的所有数据。我不知道如何加入这些，以便它显示null而不是重复。你可以直接指出我吗？

这是场景：

• 表1 =帐户：帐户名称和帐户ID


• 表2 =属性：属性说明，属性地址和帐户ID


• 表3 =车辆：车辆制造商，车辆模型和帐户ID

数据看起来像这样：

  [表1 =帐户] 
 id名称accountid 
 
 1家庭帐户001 
 2家庭B account002 
 3 Family C account003 
 
 
 
 [表2 =属性] 
 id accountid描述地址
 
 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 
 
 
 [表3 =车辆] 
 
 id描述make model accountid 
 1 green Acura Integra ac count001 
 2蓝色阿斯顿·马丁Vantage account001 
 3银色奥迪Quattro account001 
 4工作本地大陆GTC account002 
 5孩子福特Fusion混合帐户$ 2 
 6妈妈的车路虎LR4 account003 
 7纸张重量Mini Clubman account003 
 8打击者道奇口径account003 
 9为什么Mahindra TR40 account003 
 10儿童Scion xB account003 
  / pre> 
 
 
我的愿望是创建一个视图，显示数据库中的所有记录。对于每一行，我想显示帐户名称，然后显示表格中的数据。
 
 
 
我正在寻找视图来返回结果，如下所示：如果该数据没有到达当前查询的列，那么它根本不显示行上的数据。
  account_Name | property_DESCRIPTION | property_ADDRESS | vehicle_DESCRIPTION vehicles_MAKE | vehicles_MODEL 
 
  - 家庭加利福尼亚州的家庭** null null null ** 
  - 家庭A沙滩墨西哥** null null null ** 
  - 家庭A ** null null **蓝色阿斯顿·马丁Vantage 
  - 家庭A ** null null **银色奥迪Quattro 
  - 家庭B藏身处亚利桑那州** null null null ** 
  - 家庭B逃离内华达州** null null null ** 
  - 家庭B skilodge idaho ** null null null ** 
  - 家庭B ** null null **儿童福特Fusion混合
  
但是，它显示数据的重复，如下所示，每次找到新记录时，它会使用来自其他表的数据填充行。
  account_Name property_DESCRIPTION property_ADDRESS vehicles_DESCRIPTION vehicles_MAKE vehicles_MODEL 
 
  - 家庭加州绿色Acura Integra 
  - 家庭沙滩墨西哥绿色Acura Integra 
  - 家庭加州蓝色阿斯顿Martin Vantage 
  - 家庭A沙滩墨西哥蓝色Aston Martin Vantage 
  - 家庭A加利福尼亚州银奥迪Quattro 
  - 家庭A沙滩墨西哥银奥迪Quattro 
  - 家庭B藏身处亚利桑那州工作Bently大陆GTC 
  - 家庭B逃离内华达州工作Bently大陆GTC 
  - 家庭B skilodge爱达荷州工作Bently大陆GTC 
  - 家庭B藏身处亚利桑那州的孩子福特Fusion混合
  - 家庭B逃脱内华达孩子福特融合混合
  - 家庭B skilodge爱达荷孩子福特融合混合
  
为什么会发生似乎对我有意义这一切都在连接。就像加入一样，他们真的需要显示数据，无论成本如何。从脚本的角度来看，似乎不是连接，我需要单独循环，然后将结果循环加在一起。
 
 
 
从本质上讲，我需要视图首先显示表1中的所有记录，然后从表2中显示所有记录，然后从表3中显示所有记录。我不希望一起加入这些数据;我只想在一个视图中看到这一切。
 
 
 
任何人都可以告诉我如何产生上面所需的视图，显示null而不是重复的？
 
 
 
这是在SQL 2008 R2企业版。
解决方案
你需要在这种情况下使用UNION（实际为UNION ALL）。
 选择a.name作为account_Name，
 p.description作为property_DESCRIPTION，
 p.address as property_ADDRESS，
 null as vehicles_DESCRIPTION，
 null as vehicles_MAKE，
 null as vehicles_MODEL 
 from Accounts a 
 inner join属性a.accountid = p.accountid 
 UNION ALL 
选择a.name作为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 
内部加入车辆v 
 on.accountid = v.accountid 
  
 
Whenever I create a view of 3 tables, it duplicates all data on all rows.  I'm not figuring out how to join these so that it shows nulls instead of duplicates.  Can you point me in the right direct?

Here's the scenario:


Table 1 = Accounts:  Account Names and Account ID's
Table 2 = Properties: Property Description, Property Address, and Account ID's
Table 3 = Vehicles: Vehicle Make, Vehicle Model, and Account ID's


The data looks something like this:
[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
My desire is to create a view that shows me all records in the DB.  For each row I'd like to show the account name and then the data from the tables.

I'm looking for the view to return results that look like the following:  Where it simply doesn't display data on the row if that data did not come the column that it was currently querying.
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
But instead, it displays duplicates of data, like the following where every time it finds a new records, it populates the row with data from other tables.
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
Why this is occurring seems to make sense to me; it's all in the joins.  It's as though joins feel like they really need to display data no matter the cost. Thinking from a scripting point of view, it seems like instead of joins, I need to loop through table individually and then join the resulting loops together.

In essence I need the view to first show all records from table 1, then all records from table 2, then all records from table 3.  I don't really want to join this data together; I just want to see it all within a single view.

Can anyone clue me in to how to produce the desired view above that shows nulls instead of duplicates?

This is on SQL 2008 R2 Enterprise.
解决方案
You'd need to use a UNION (actually UNION ALL) in this case.
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


                        
这篇关于SQL视图：连接表不会导致数据在每一行重复？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！