从相关子表创建视图

admin

从相关子表创建视图

sql

我具有以下常规表格结构（在我的作过的示例中原谅了以美国为中心的汽车制造商）：

CREATE TABLE Car (
    [Id] int PRIMARY KEY
)

CREATE TABLE Ford (
    [FordId] int PRIMARY KEY, --also a foreign key on Car
    [Model]  nvarchar(max)
)

CREATE TABLE Chevy (
    [ChevyId] int PRIMARY KEY, --also a foreign key on Car
    [Model]  nvarchar(max)
)

我想在这些表的顶部创建一个视图，以便可以检索所有Fords和Chevys，并且在视图中只有一个生成的列可以告诉我。我的第一个刺是：

SELECT 
    c.CarId,
    case when f.FordId is not null then 'Ford' else 'Chevy' end 
FROM Car as c
LEFT JOIN Ford as f on c.Id = f.FordId
LEFT JOIN Chevy as ch on c.Id = ch.ChevyId
WHERE (f.FordId is not null or ch.ChevyId is not null)

但这会使我口中难闻，我担心表现。我会更好地以单独的CTE值检索所有的福特和雪佛兰，然后对它们进行联合吗？我是否完全走错了轨道？我还将需要包括Model列（以及两个子表共有的其他一些列），这显然会使我的观点变成一系列巨大的case语句。处理这种情况的“正确”方法是什么？

编辑：以为我应该补充一点，该架构已经存在，因此无法更改基础表。

阅读 154

2021-07-01

共1个答案

admin

首先，让我们尝试看看两种方法各自的优缺点：

create view vw_Car1
as
  SELECT 
      c.Id,
      case when f.FordId is not null then 'Ford' else 'Chevy' end as Maker,
      coalesce(f.Model, ch.Model) as Model
  FROM Car as c
  LEFT JOIN Ford as f on c.Id = f.FordId
  LEFT JOIN Chevy as ch on c.Id = ch.ChevyId
  WHERE (f.FordId is not null or ch.ChevyId is not null);

create view vw_Car2
as
  select FordId as id, 'Ford' as Maker, Model from Ford
  union all
  select ChevyId as id, 'Chevy' as Maker, Model from Chevy;

当在联接中使用它时，第一个更好，特别是如果您不使用所有列。例如，假设您在使用时拥有一个视图vw_Car：

create table people (name nvarchar(128), Carid int);

insert into people
select 'John', 1 union all
select 'Paul', 2;

create view vw_people1
as
select
    p.Name, c.Maker, c.Model
from people as p
   left outer join vw_Car1 as c on c.ID = p.CarID;

create view vw_people2
as
select
    p.Name, c.Maker, c.Model
from people as p
   left outer join vw_Car2 as c on c.ID = p.CarID;

现在，如果要进行简单选择，请执行以下操作：

select Name from vw_people1;

select Name from vw_people2;

第一个是简单的选择people（vw_Car1根本不会查询）。第二个将更为复杂-Ford而且Chevy将会对其进行查询。您可能认为第一种方法更好，但是让我们尝试另一个查询：

select *
from vw_people1
where Maker = 'Ford' and Model = 'Fiesta';

select *
from vw_people2
where Maker = 'Ford' and Model = 'Fiesta';

在这里第二个会更快，尤其是当您在Model列上有索引时。

= > sql fiddle演示-请参阅这些查询的查询计划。

2021-07-01