两个表做关联查询...

cqhweb 2011-05-19 07:21:23
已知两表如下:
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


...全文
80 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
--小F-- 2011-05-19
  • 打赏
  • 举报
回复
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])
中国风 2011-05-19
  • 打赏
  • 举报
回复
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
*/
叶子 2011-05-19
  • 打赏
  • 举报
回复

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


楼主给出的结果和需求不对应。
打一壶酱油 2011-05-19
  • 打赏
  • 举报
回复

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'
yoan_T 2011-05-19
  • 打赏
  • 举报
回复
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'

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧