从相关子表创建视图 [英] Creating View from Related Child Tables
问题描述
我具有以下常规表结构(在我的人为例子中原谅以美国为中心的汽车制造商):
I have the following general table structure (forgive the United States-centric carmakers in my contrived example):
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,而视图中只有一个生成的列可以告诉我品牌。我的第一个刺是这样的:
I am wanting to create a view on top of these tables so that I can retrieve all Fords and Chevys and just have a generated column in the view that tells me the make. My first stab was this:
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语句。处理这种情况的正确方法是什么?
But that leaves a bad taste in my mouth and I am concerned about performance. Would I be better off retrieving all the Fords and Chevys in separate CTE values and just performing a union on them? Am I on the wrong track entirely? I will also be needing to include the Model column (as well as a few other columns common to the two child tables), which would obviously make my view turn into a giant series of case statements. What is the "proper" way to handle such a situation?
编辑:以为我应该添加此模式,因此请更改基础
EDIT: Thought I should add that this schema already exists so changing the underlying tables is not possible.
推荐答案
首先,让我们尝试看看两种方法的优缺点:
First of all, let's try to see pros and cons of each of 2 approaches:
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
时拥有视图:
The first one is better when you use it in joins, especially if you'll not using all of your columns. For example, let's say you have a view when you're using your 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;
现在,如果要进行简单选择,则:
Now, if you want to do simple select:
select Name from vw_people1;
select Name from vw_people2;
第一个简单的选择是个人
(根本不会查询 vw_Car1
)。第二个会更复杂-将同时查询 Ford
和
您可能会认为第一种方法更好,但是让我们尝试另一个查询:
First one would be simple select from people
(vw_Car1
will not be queried at all). Second one will be more complex - Ford
and Chevy
will be both queried.
You could think that first approach is better, but let's try another query:
select *
from vw_people1
where Maker = 'Ford' and Model = 'Fiesta';
select *
from vw_people2
where Maker = 'Ford' and Model = 'Fiesta';
这里第二个会更快,特别是如果您在 Model
列。
Here second one will be faster, especially if you have index on Model
column.
=> sql fiddle演示 -查看这些查询的查询计划。
=> sql fiddle demo - see query plans of these queries.
这篇关于从相关子表创建视图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!