--> 测试数据:#ta if object_id('tempdb.dbo.#ta') is not null drop table #ta go create table #ta([单号] varchar(3),[货物] varchar(4),[客户] varchar(4)) insert #ta select '001','B001','A001' union all select '002','B001','A001' union all select '003','B001','A001' union all select '004','B001','A002'--------------------------------查询开始--------------------------------> 测试数据:#tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb([单号] varchar(3),[货物] varchar(4),[客户] varchar(4)) insert #tb select '001','B001','A001' union all select '002','B001','A001' union all select '003','B001','A001' union all select '004','B001','A002' union all select '005','B002','A001'--------------------------------查询开始------------------------------ select * from #ta a where exists (select 1 from #tb b where a.[单号]=b.[单号] and a.[货物]=b.[货物] and a.[客户]=b.[客户]) /* 单号 货物 客户 ---- ---- ---- 001 B001 A001 002 B001 A001 003 B001 A001 004 B001 A002(4 行受影响) */
--> 测试数据:#ta if object_id('tempdb.dbo.#ta') is not null drop table #ta go create table #ta([进货日期] datetime,[客户] varchar(4),[货物] varchar(4)) insert #ta select '2010-05-01','C001','0001' union all select '2010-05-12','C001','0001' union all select '2010-05-14','C001','0016' union all select '2010-05-11','C001','0014' union all select '2010-05-09','C012','0001' union all select '2010-05-09','C012','0175'--------------------------------查询开始------------------------------select * from #ta a where not exists(select 1 from #ta where 进货日期>dateadd(d,-3,getdate()) and[客户]=a.[客户] and [进货日期]>a.[进货日期]) and 进货日期<dateadd(d,-3,getdate()) /* 进货日期 客户 货物 ----------------------- ---- ---- 2010-05-09 00:00:00.000 C012 0001 2010-05-09 00:00:00.000 C012 0175(2 行受影响) */
select a.货物,a.客户,a.单号 as 单号1,b.单号 as 单号2 from 表1 a,表2 b where a.货物=b.货物 and a.客户=b.客户
select a.*
from tab a join tab1 b on a.fld=b.fld and a.col(字段名)=b.col(字段名)是不是这个意思?
单号 货物 客户
001 B001 A001
002 B001 A001
003 B001 A001
004 B001 A002
表2
单号 货物 客户
001 B001 A001
002 B001 A001
003 B001 A001
004 B001 A002
005 B002 A001求:找出两表货物和客户都相同的记录
进货日期 客户 货物
2010-05-01 C001 0001
2010-05-12 C001 0001
2010-05-14 C001 0016
2010-05-11 C001 0014
2010-05-09 C012 0001
2010-05-09 C012 0175
求:查找以上表中,最近3天都未进货的客户和货物(客户和货物是对应的,客户C001的货物0001和C012货物0001是不同的货物).
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta([单号] varchar(3),[货物] varchar(4),[客户] varchar(4))
insert #ta
select '001','B001','A001' union all
select '002','B001','A001' union all
select '003','B001','A001' union all
select '004','B001','A002'--------------------------------查询开始--------------------------------> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([单号] varchar(3),[货物] varchar(4),[客户] varchar(4))
insert #tb
select '001','B001','A001' union all
select '002','B001','A001' union all
select '003','B001','A001' union all
select '004','B001','A002' union all
select '005','B002','A001'--------------------------------查询开始------------------------------
select * from #ta a where exists
(select 1 from #tb b where a.[单号]=b.[单号] and a.[货物]=b.[货物] and a.[客户]=b.[客户])
/*
单号 货物 客户
---- ---- ----
001 B001 A001
002 B001 A001
003 B001 A001
004 B001 A002(4 行受影响)
*/
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta([进货日期] datetime,[客户] varchar(4),[货物] varchar(4))
insert #ta
select '2010-05-01','C001','0001' union all
select '2010-05-12','C001','0001' union all
select '2010-05-14','C001','0016' union all
select '2010-05-11','C001','0014' union all
select '2010-05-09','C012','0001' union all
select '2010-05-09','C012','0175'--------------------------------查询开始------------------------------select * from #ta a where
not exists(select 1 from #ta where 进货日期>dateadd(d,-3,getdate()) and[客户]=a.[客户] and [进货日期]>a.[进货日期])
and 进货日期<dateadd(d,-3,getdate())
/*
进货日期 客户 货物
----------------------- ---- ----
2010-05-09 00:00:00.000 C012 0001
2010-05-09 00:00:00.000 C012 0175(2 行受影响)
*/