两个表做关联查询...

[cqhweb]

已知两表如下：
A表
交易码 类型
transcode type
AA 01
BB 01
CC 01
DD 02
EE 03
................

B表
交易码 交易日期
jnl transcode transdate
1 AA 2011-04-12
2 BB 2011-05-01
3 CC 2011-05-10
4 DD 2011-05-17
5 EE 2011-05-20
6 CC 2011-05-25
7 FF 2011-05-31
............................

求日期 transdate>=2011-05-10 并且transdate<=2011-05-20 之间,没有出现type=01的交易.
即结果如下：
jnl transcode transdate
1 AA 2011-04-12
2 BB 2011-05-01
6 CC 2011-05-25

[yoan_T]

select b.* , a.* from b left join on b.transcode = a.transcode where b.transdate between '2011-05-10' and '2011-05-20' and a.type <> '01'

[打一壶酱油]

 select b.* from A表 a,B表 b 
 where a.type = b.transcode
 and b.transdate >=  '2011-05-10' and  b.transdate<= '2011-05-20'
 and a.type <> '01'

[叶子]

declare @A表 table (transcode varchar(4),type varchar(4))
insert into @A表
select 'AA','01' union all
select 'BB','01' union all
select 'CC','01' union all
select 'DD','02' union all
select 'EE','03'

declare @B表 table 
(jnl int,transcode varchar(4),transdate varchar(10))
insert into @B表
select '1','AA','2011-04-12' union all
select '2','BB','2011-05-01' union all
select '3','CC','2011-05-10' union all
select '4','DD','2011-05-17' union all
select '5','EE','2011-05-20' union all
select '6','CC','2011-05-25' union all
select '7','FF','2011-05-31'

select b.* from @B表 b 
left join @A表 a on a.transcode=b.transcode
where b.transdate between '2011-05-10' and '2011-05-20'
and a.type<>'01'

/*
jnl         transcode transdate
----------- --------- ----------
4           DD        2011-05-17
5           EE        2011-05-20
*/

楼主给出的结果和需求不对应。

[中国风]

use tempdb
go
 
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#1') is null
	drop table #1
Go
Create table #1([transcode] nvarchar(2),[type] nvarchar(2))
Insert #1
select N'AA',N'01' union all
select N'BB',N'01' union all
select N'CC',N'01' union all
select N'DD',N'02' union all
select N'EE',N'03'
Go
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#2') is null
	drop table #2
Go
Create table #2([jnl] int,[transcode] nvarchar(2),[transdate] Datetime)
Insert #2
select 1,N'AA','2011-04-12' union all
select 2,N'BB','2011-05-01' union all
select 3,N'CC','2011-05-10' union all
select 4,N'DD','2011-05-17' union all
select 5,N'EE','2011-05-20' union all
select 6,N'CC','2011-05-25' union all
select 7,N'FF','2011-05-31'
Go
select 
b.*
from #1 as a
	inner join #2 as b on a.transcode=b.transcode and a.type='01'
where not exists(

Select 
	1 
from  #2  
where [transdate] between '2011-05-10' and '2011-05-20'
and [jnl]=b.[jnl]
)

/*
jnl	transcode	transdate
1	AA	2011-04-12 00:00:00.000
2	BB	2011-05-01 00:00:00.000
6	CC	2011-05-25 00:00:00.000
*/

[--小F--]

select 
  b.*
from
 a join b 
on 
  a.transcode=b.transcode and a.type='01'
where 
  not exists(Select 1 from b  where [transdate] between '2011-05-10' and '2011-05-20'and [jnl]=b.[jnl])