可以在视图中使用连接上的 where 子句吗 [英] Can a where clause on a join be used in a view

问题描述

我正在尝试创建一个 SQL 视图，该视图合并了许多单独的选择查询.我在将各个 select 语句中的子句放入数据库视图时遇到了一些困难.

我的观点的简化版本是:

创建或替换视图 TestView 为选择一个名字，B.订阅，C.到期从TestTableA as A 左外连接 TestTableB as B on A.ID = B.A_ID在 A.ID = C.A_ID 上左外连接 TestTableC 作为 C；

我的视图有两个问题:

• 在第一次加入时，我如何只选择订阅是特定值的记录，如果不是该值，仍然检索名称和到期列(在这种情况下，订阅将为空)?

• 在第二次加入时，如何指定我只想要最近到期的记录?

以下是我的测试模式、示例数据和所需的结果集:

创建表TestTableA(身份证号码，名称 varchar(32),主键(ID));创建表 TestTableB(身份证号码，A_ID 整数，订阅 varchar(32),主键(ID)，外键(A_ID)引用 TestTableA(ID));创建表 TestTableC(身份证号码，A_ID 整数，到期日，主键(ID)，外键(A_ID)引用 TestTableA(ID));创建或替换视图 TestView 为选择一个名字，B.订阅，C.到期从TestTableA as A 左外连接 TestTableB as B on A.ID = B.A_ID在 A.ID = C.A_ID 上左外连接 TestTableC 作为 C；插入 TestTableA 值 (1, 'Joe');插入 TestTableB 值 (1, 1, 'abcd');插入 TestTableB 值 (2, 1, 'efgh');插入 TestTableC 值 (1, 1, '2012-10-25');插入 TestTableC 值 (2, 1, '2012-10-24');插入 TestTableA 值 (2, 'Jane');

预期结果 1:

select * from TestView where Subscription is null or Subscription = 'efgh';乔, efgh, 2012-10-25简，，

预期结果 2:

select * from TestView where Subscription is null or Subscription = 'xxxx';乔, 2012-10-25简，，

解决方案

我会用简单的 SQL 编写查询
如果您有 SQL Server 2005 或更高版本，则可以使用 outer apply 而不是使用 min()

连接子查询

选择一个名字，B.订阅，C.到期从 TestTableA 作为 A左外连接 TestTableB 作为 B 在 A.ID = B.A_ID 和 B.Subscription in ('abcd', 'efgh')左外连接(选择 min(T.Expiry) 作为到期，T.A_ID从 TestTableC 作为 T按 T​​.A_ID 分组) 作为 A.ID = C.A_ID 上的 C

I'm attempting to create an SQL view that consolidates a number of separate select queries. I've encountered some difficulty putting clauses from the individual select statements into the database view.

A simplified version of my view is:

create or replace view TestView as
select 
 A.Name,
 B.Subscription,
 C.Expiry
from
 TestTableA as A left outer join TestTableB as B on A.ID = B.A_ID
 left outer join TestTableC as C on A.ID = C.A_ID;

I've got two problems with the view:

• On the frist join how can I only select record where the Subscription is a specific value AND if it is not that value still retrieve the Name and Expiry columns (in which case the Subscription would be null)?

• On the second join how can I specify I only want the record with the most recent expiry date?

Below is my test schema, sample data and desired result set:

create table TestTableA
(
    ID int,
    Name varchar(32),
    Primary Key(ID)
);

create table TestTableB
(
    ID int,
    A_ID int,
    Subscription varchar(32),
    Primary Key(ID),
    Foreign Key(A_ID) references TestTableA(ID)
);

create table TestTableC
(
    ID int,
    A_ID int,
    Expiry date,
    Primary Key(ID),
    Foreign Key(A_ID) references TestTableA(ID)
);

create or replace view TestView as
select 
 A.Name,
 B.Subscription,
 C.Expiry
from
 TestTableA as A left outer join TestTableB as B on A.ID = B.A_ID
 left outer join TestTableC as C on A.ID = C.A_ID;

insert into TestTableA values (1, 'Joe');
insert into TestTableB values (1, 1, 'abcd');
insert into TestTableB values (2, 1, 'efgh');
insert into TestTableC values (1, 1, '2012-10-25');
insert into TestTableC values (2, 1, '2012-10-24');
insert into TestTableA values (2, 'Jane');

Desired Results 1:

select * from TestView where Subscription is null or Subscription = 'efgh';

Joe, efgh, 2012-10-25
Jane, , 

Desired Results 2:

select * from TestView where Subscription is null or Subscription = 'xxxx';

Joe, , 2012-10-25
Jane, , 

解决方案

I'll write query with simple SQL
If you have SQL Server 2005 or higher, you can use outer apply instead of join on subquery with min()

select 
    A.Name,
    B.Subscription,
    C.Expiry
from TestTableA as A
    left outer join TestTableB as B on A.ID = B.A_ID and B.Subscription in ('abcd', 'efgh') 
    left outer join
    (
        select min(T.Expiry) as Expiry, T.A_ID
        from TestTableC as T
        group by T.A_ID
    ) as C on A.ID = C.A_ID

这篇关于可以在视图中使用连接上的 where 子句吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！