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早的那条记录。谢谢
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早的那条记录。谢谢
解决方案 »
- SQL 存储过程中日期参数的问题
- 求按根据最后回帖排倒序 显示主贴标题列表的SQL语句
- DateTime比较的问题
- 每日一练 进来练一下
- [求助]数据库日志压缩
- 我的SQLserver中的有个字段是时间日期型的,可是现在有一部分不知道怎么都变成"YYYY-MM-DD 09:17:00"???
- tempdb日志
- 如何从一个不断更新的数据库中读取最新的数据?
- 用什么样的SQL能实现这种功能,我想了好几天都解决不了,请高手帮忙,在线等待。。。。
- 如何让数据库能同时显示中文和韩文啊,我用的是Progress.
- top 1 返回值赋给变量怎么做
- sql server 存储过程将一系列查询结果输出到txt文档中保存?
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'
GO
--建立視圖
Create View V
As
Select
A.BookNo,
A.Pages,
D.MadeFrom,
D.[Date]
From
A
Left Join
(Select
B.*
From
B
Inner Join
(Select BookNo, Min([Date]) As [Date] From B Group By BookNo) C
On B.BookNo = C.BookNo And B.[Date] = C.[Date]) D
On A.BookNo = D.BookNo
GO
--測試
Select * From V
GO
--刪除測試環境
Drop Table A, B
Drop View V
--結果
/*
BookNo Pages MadeFrom Date
201 100 shanghai 2001-12-12
202 50 NULL NULL
*/
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 行)*/
--如果用Exists的方法,不用像樓上寫的那個複雜。--建立測試環境
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'
GO
--建立視圖
Create View V
As
Select
A.BookNo,
A.Pages,
B.MadeFrom,
B.[Date]
From
A
Left Join
B
On A.BookNo = B.BookNo
Where Not Exists (Select BookNo From B C Where C.BookNo = B.BookNo And C.[Date] < B.[Date])
GO
--測試
Select * From V
GO
--刪除測試環境
Drop Table A, B
Drop View V
--結果
/*
BookNo Pages MadeFrom Date
201 100 shanghai 2001-12-12
202 50 NULL NULL
*/