我现在有两张表,如下:
table 1
date id
2009-01-01 001
2009-02-01 001
2009-03-01 001
2009-04-01 001
2009-05-01 001
2009-06-01 001
2009-01-01 002
2009-02-01 002
2009-03-01 002table 2
date id
2009-05-01 001
2009-02-01 002
结果
想在table1中查找出,小于table2中的日期区间,结果如下
date id
2009-01-01 001
2009-02-01 001
2009-03-01 001
2009-04-01 001
2009-05-01 001
2009-01-01 002
2009-02-01 002
table 1
date id
2009-01-01 001
2009-02-01 001
2009-03-01 001
2009-04-01 001
2009-05-01 001
2009-06-01 001
2009-01-01 002
2009-02-01 002
2009-03-01 002table 2
date id
2009-05-01 001
2009-02-01 002
结果
想在table1中查找出,小于table2中的日期区间,结果如下
date id
2009-01-01 001
2009-02-01 001
2009-03-01 001
2009-04-01 001
2009-05-01 001
2009-01-01 002
2009-02-01 002
insert into table1 select '2009-01-01','001'
insert into table1 select '2009-02-01','001'
insert into table1 select '2009-03-01','001'
insert into table1 select '2009-04-01','001'
insert into table1 select '2009-05-01','001'
insert into table1 select '2009-06-01','001'
insert into table1 select '2009-01-01','002'
insert into table1 select '2009-02-01','002'
insert into table1 select '2009-03-01','002'
create table table2(date datetime,id varchar(5))
insert into table2 select '2009-05-01','001'
insert into table2 select '2009-02-01','002'
go
select * from table1 a where exists(select 1 from table2 where id=a.id and [date]>=a.[date])
go
drop table table1,table2
/*
date id
----------------------- -----
2009-01-01 00:00:00.000 001
2009-02-01 00:00:00.000 001
2009-03-01 00:00:00.000 001
2009-04-01 00:00:00.000 001
2009-05-01 00:00:00.000 001
2009-01-01 00:00:00.000 002
2009-02-01 00:00:00.000 002
*/
select * from table1 where date < (select top 1 date from table2 order by date)
insert into #tb select '2009-01-01','001'
insert into #tb select '2009-02-01','001'
insert into #tb select '2009-03-01','001'
insert into #tb select '2009-04-01','001'
insert into #tb select '2009-05-01','001'
insert into #tb select '2009-06-01','001'
insert into #tb select '2009-01-01','002'
insert into #tb select '2009-02-01','002'
insert into #tb select '2009-03-01','002'
create table #tb2(date datetime,id varchar(5))
insert into #tb2 select '2009-05-01','001'
insert into #tb2 select '2009-02-01','002' select * from #tb t1 where exists(select 1 from #tb2 where id=t1.id and [date]>=t1.[date])date id
----------------------- -----
2009-01-01 00:00:00.000 001
2009-02-01 00:00:00.000 001
2009-03-01 00:00:00.000 001
2009-04-01 00:00:00.000 001
2009-05-01 00:00:00.000 001
2009-01-01 00:00:00.000 002
2009-02-01 00:00:00.000 002(7 行受影响)
*
from
table1 t
where
[date] < (select top 1 [date] from table2 where id=t.id order by [date] desc)
select a.* from table1 a,table2 b where a.ID = b.ID and a.[date]<=b.[date]
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-16 17:03:55
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
Go
CREATE TABLE tb1(date SMALLDATETIME,id NVARCHAR(3))
Go
INSERT INTO tb1
SELECT '2009-01-01','001' UNION ALL
SELECT '2009-02-01','001' UNION ALL
SELECT '2009-03-01','001' UNION ALL
SELECT '2009-04-01','001' UNION ALL
SELECT '2009-05-01','001' UNION ALL
SELECT '2009-06-01','001' UNION ALL
SELECT '2009-01-01','002' UNION ALL
SELECT '2009-02-01','002' UNION ALL
SELECT '2009-03-01','002'
GOSELECT * FROM TB1-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-16 17:05:59
IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
Go
CREATE TABLE tb2(date SMALLDATETIME,id NVARCHAR(3))
Go
INSERT INTO tb2
SELECT '2009-05-01','001' UNION ALL
SELECT '2009-02-01','002'
GOSELECT * FROM TB2select distinct tb1.*
from tb1,tb2 s ,tb2 t
where tb1.date between s.date and t.date or tb1.date between t.date and s.date
order by iddate id
2009-02-01 00:00:00 001
2009-03-01 00:00:00 001
2009-04-01 00:00:00 001
2009-05-01 00:00:00 001
2009-02-01 00:00:00 002
2009-03-01 00:00:00 002