A表
BookNo Pages
201 100
202 50
B表
BookNo MadeFrom Date
201 shanghai 2001-12-12
201 beijing 2002-01-02
视图：
V
BookNo Pages MadeFrom Date
201 100 shanghai 2001-12-12
202 50 N N
A与B关联，A表全列出，如果B表重复只列出Date早的那条记录。
谢谢
------解决方案--------------------
try
Create View V
As
Select
A.BookNo,
A.Pages,
C.MadeFrom,
C.[Date] As [Date]
From
A
Left Join
B
On A.BookNo = B.BookNo
Left Join
(Select BookNo, Min([Date]) As [Date] From B Group By BookNo) C
On B.BookNo = C.BookNo And B.[Date] = C.[Date]
GO
------解决方案--------------------
Create Table A
(Bookno Varchar(10),
Pages Int)
Insert A Select '201 ', 100
Union All Select '202 ', 50
Create Table B
(BookNo Varchar(10),
MadeFrom Varchar(10),
[Date] Varchar(10))
Insert B Select '201 ', 'shanghai ', '2001-12-12 '
Union All Select '201 ', 'beijing ', '2002-01-02 '
create view viewname
as
select bookno,pages,madefrom, date
from (select a.*,b.madefrom,b.date from a left join b
on a.bookno=b.bookno ) aa
where not exists ( select 1 from (select a.*,b.madefrom,b.date from a left join b
on a.bookno=b.bookno ) bb where aa.bookno=bb.bookno and aa.date <bb.date)
select * from viewname
/*
bookno pages madefrom date
---------- ----------- ---------- ----------
201 100 beijing 2002-01-02
202 50 NULL NULL
（所影响的行数为 2 行）
*/