/*
表A
vbeln erdat prdha
0090056842 2009-12-02 00:00:00.000 D0EXBX133X
0090058691 2009-12-18 00:00:00.000 D0EXBX133X
表B
erdat1 prdha price rate
2009-12-01 00:00:00.000 D0EXBX133X 90.52 0.020
2009-12-03 00:00:00.000 D0EXBX133X 92.86 0.050
2009-12-10 00:00:00.000 D0EXBX133X 93.88 0.030
2009-12-14 00:00:00.000 D0EXBX133X 95.20 0.080
2009-12-18 00:00:00.000 D0EXBX667X 98.20 0.040希望A表能从B表中取得小于它时间的最近的时间的那条记录,即最终结果为:
vbeln erdat prdha price rate
0090056842 2009-12-02 00:00:00.000 D0EXBX133X 90.52 0.020
0090058691 2009-12-18 00:00:00.000 D0EXBX133X 95.20 0.080
*/
not in (select id from tb) --有多个
为什么
not exist ( select top 1 id from tb) --只有一个
?
表A
vbeln erdat prdha
0090056842 2009-12-02 00:00:00.000 D0EXBX133X
0090058691 2009-12-18 00:00:00.000 D0EXBX133X
表B
erdat1 prdha price rate
2009-12-01 00:00:00.000 D0EXBX133X 90.52 0.020
2009-12-03 00:00:00.000 D0EXBX133X 92.86 0.050
2009-12-10 00:00:00.000 D0EXBX133X 93.88 0.030
2009-12-14 00:00:00.000 D0EXBX133X 95.20 0.080
2009-12-18 00:00:00.000 D0EXBX667X 98.20 0.040希望A表能从B表中取得小于它时间的最近的时间的那条记录,即最终结果为:
vbeln erdat prdha price rate
0090056842 2009-12-02 00:00:00.000 D0EXBX133X 90.52 0.020
0090058691 2009-12-18 00:00:00.000 D0EXBX133X 95.20 0.080
*/
not in (select id from tb) --有多个
为什么
not exist ( select top 1 id from tb) --只有一个
?
( select top 1 id from tb)--返回的ID只有1个
把top 1 去掉看看[/code]
not exist ( select 1 from tb where id=tb.id)
not exist 是判断括号里面是否存在值即可,只要一个是提高速度而已,其实也可以多个,这样写是效率的考虑,最好用 not exists (select 1 from tb) 这样更快。
这是返回一个BOOLEAN值
declare @ta table([vbeln] varchar(10),[erdat] datetime,[prdha] varchar(10))
insert @ta
select '0090056842','2009-12-02 00:00:00.000','D0EXBX133X' union all
select '0090058691','2009-12-18 00:00:00.000','D0EXBX133X'--> 测试数据:@tb
declare @tb table([erdat1] datetime,[prdha] varchar(10),[price] numeric(4,2),[rate] numeric(4,3))
insert @tb
select '2009-12-01 00:00:00.000','D0EXBX133X',90.52,0.020 union all
select '2009-12-03 00:00:00.000','D0EXBX133X',92.86,0.050 union all
select '2009-12-10 00:00:00.000','D0EXBX133X',93.88,0.030 union all
select '2009-12-14 00:00:00.000','D0EXBX133X',95.20,0.080 union all
select '2009-12-18 00:00:00.000','D0EXBX667X',98.20,0.040SELECT A.*,B.price,B.rate
FROM @ta a ,@tb b WHERE A.[prdha]=B.[prdha]
and not exists( select 1 from @tb where b.[erdat1]>[erdat] and [prdha]=b.[prdha])查询结果好像不正确...
希望A表能从B表中取得小于它时间的最近的时间的那条记录,即最终结果为:
vbeln erdat prdha price rate
0090056842 2009-12-02 00:00:00.000 D0EXBX133X 90.52 0.020
0090058691 2009-12-18 00:00:00.000 D0EXBX133X 95.20 0.080
--测试数据
if object_id('A','U') is not null
drop table A;
gocreate table A(
vbeln nvarchar(30),
erdat datetime,
prdha nvarchar(30)
)
goinsert into A
select '0090056842','2009-12-02 00:00:00.000','D0EXBX133X' union all
select '0090058691','2009-12-18 00:00:00.000','D0EXBX133X'
goif object_id('B','U') is not null
drop table B;
gocreate table B(
erdat1 datetime,
prdha nvarchar(30),
price numeric(38,2),
rate numeric(38,4)
)
goinsert into B
select '2009-12-01 00:00:00.000','D0EXBX133X',90.52,0.020 union all
select '2009-12-03 00:00:00.000','D0EXBX133X',92.86,0.050 union all
select '2009-12-10 00:00:00.000','D0EXBX133X',93.88,0.030 union all
select '2009-12-14 00:00:00.000','D0EXBX133X',95.20,0.080 union all
select '2009-12-18 00:00:00.000','D0EXBX667X',98.20,0.040
go--查询
select t.vbeln,t.erdat,t.prdha,B.price,B.rate
from (
select A.vbeln,A.erdat,A.prdha,min(B.erdat1) as B_redat1_min
from A,
B
where A.prdha = B.prdha
and A.erdat >= B.erdat1
group by A.vbeln,A.erdat,A.prdha
) t,
B
where t.prdha = B.prdha
and t.B_redat1_min = B.erdat1--结果
/*
0090056842 2009-12-02 00:00:00.000 D0EXBX133X 90.52 0.0200
0090058691 2009-12-18 00:00:00.000 D0EXBX133X 90.52 0.0200
*/