可以在加入一个视图中使用的where子句(Can a where clause on a join

我试图创建，它整合了一些单独的选择查询的SQL视图。我已经遇到了一些困难，把条文从单个select语句到数据库视图。

我的观点的简化版本是：

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;

我有两个问题的看法：

在弗里斯特加入我怎么可以只选择记录下认购是一个特定的值，如果它不是价值仍然检索名称和到期列（在这种情况下，认购会为null）？
关于第二个加入我怎么可以指定我只想与最近到期日的记录？

下面是我的测试模式，样本数据和期望的结果集：

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');

期望的结果1：

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

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

期望的结果2：

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

Joe, , 2012-10-25
Jane, ,

Answer 1:

我会写简单的SQL查询
如果您有SQL Server 2005或更高版本，可以使用outer apply ，而不是与子查询加入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

Answer 2:

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;
where 
  B.Subscription is not null
  and C.Expiry between (now() - interval 1 minute) and now()