求sql语句使用not exists

[cqhweb]

/* 
表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) --只有一个
?

[快乐_石头]

[code=SQL](select  id  from  tb) --返回的ID 可以多个
(  select  top  1  id  from  tb)--返回的ID只有1个

把top 1 去掉看看[/code]

[duanzhi1984]

not in (select id from tb)
not exist ( select 1 from tb where id=tb.id)

[andysun88]

not in 是不包含括号里面的
not exist 是判断括号里面是否存在值即可，只要一个是提高速度而已，其实也可以多个，这样写是效率的考虑，最好用 not exists (select 1 from tb) 这样更快。

[快乐_石头]

[Quote=引用 1 楼 happy_stone 的回复:]
SQL code

(select  idfrom  tb)--返回的ID 可以多个(selecttop1  idfrom  tb)--返回的ID只有1个
把top 1 去掉看看

[/Quote]
貌似我理解錯了
應該還有其他條件把
幫頂
下班了

[SQL77]

not exist ( select top 1 id from tb)
这是返回一个BOOLEAN值

[cqhweb]

--> 测试数据：@ta
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.040



SELECT  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])

查询结果好像不正确...

[-狙击手-]

怎么不正确呀，因 为你ta也多条呀

[cqhweb]

查询不到

希望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 

[SQL77]

http://topic.csdn.net/u/20100301/17/4c8e8704-7a30-44a6-9db0-9ef60f62939b.html

你和楼主是同一个人?

[老黎]

--测试数据
if object_id('A','U') is not null
    drop table A;
go

create table A(
    vbeln  nvarchar(30),
    erdat  datetime,
    prdha  nvarchar(30)
)
go

insert into A 
select '0090056842','2009-12-02 00:00:00.000','D0EXBX133X' union all
select '0090058691','2009-12-18 00:00:00.000','D0EXBX133X'
go

if object_id('B','U') is not null
    drop table B;
go

create table B(
    erdat1  datetime,
    prdha   nvarchar(30),
    price   numeric(38,2),
    rate    numeric(38,4)
)
go

insert 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
*/

[cqhweb]

能不能用 not exists来实现