首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

简单的视图有关问题,请进来看看.

2012-01-13 
简单的视图问题,请进来看看...A表BookNoPages20110020250B表BookNoMadeFromDate201shanghai2001-12-12201b

简单的视图问题,请进来看看...
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
(BooknoVarchar(10),
PagesInt)
Insert A Select '201 ', 100
Union All Select '202 ', 50

Create Table B
(BookNoVarchar(10),
MadeFromVarchar(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 行)

*/

热点排行