高分求一SQL语句，一旦正确，马上给分！！！！

[liujiaqiang]

请高手帮忙，如何写一条SQL语句，统计出过期的记录来.
表结构如下：
table1 (code varchar(20) , name varchar(50) , remark varchar(100))
[其中Code 为主键]
table2 (code varchar(20) , Lines numeric(10) , start_date datetime ,
years integer , end_date datetime) [其中code 和 Lines为主键，
而且code 与 table1的Code 相关联 ]

假如有以下记录
table1
code name remark
Tg-001 张三
Tg-002 李四
Tg-003 王五
Tg-004 小何
Tg-005 小红
Tg-006 小张

Table2
Code Lines start_date years end_date
Tg-001 1 2002-11-21 1 2003-11-21 (此用户第一次交到2003-11-21)
Tg-001 2 2003-11-21 1 2004-11-21 (此用户第二次交到2004-11-21)
Tg-002 1 1998-03-01 1 1999-03-01
Tg-003 1 1998-02-01 2 2000-02-01
Tg-003 2 2000-02-01 8 2008-02-01 (此用户第二次交到2008-02-01)
Tg-004 1 1995-01-01 2 1997-01-01
Tg-005 1 2003-07-08 1 2004-07-08
Tg-006 1 1997-02-01 2 1999-02-01
Tg-006 2 1999-02-01 1 2000-02-01
Tg-006 3 2000-02-01 1 2002-02-01

让用户输入一个日期,想统计出以这个日期截止需要来交费的记录.
假如用户输入 : 2003-10-20日 , 如何统计得到这些记录
Code Name Start_Date years end_date
Tg-002 李四 1998-03-01 1 1999-03-01
Tg-004 小何 1995-01-01 2 1997-01-01
Tg-006 小张 2000-02-01 1 2002-02-01

求兄弟们帮帮忙！！！不胜感激！！！

[welyngj]

select
b.Code , a.Name , b.Start_Date, b.years, b.end_date
from table1 a,table2 b
where
a.code=b.code
and
b.end_date<cast( '2003-10-20' as datetime)

[zjcxc]

--查询
declare @dt datetime
set @dt='2003-10-20'

select a.code,a.name,b.Start_Date,b.years,b.end_date
from table1 a
join table2 b on a.code=b.code
join(
select code,Lines=max(Lines)
from table2 a
group by code
having max(end_date)<@dt
)c on b.code=c.code and b.Lines=c.Lines

[orcale]

select a.code,a.name,min(start_date) [start_date],max(end_date) [end_date],datediff(year,min(start_date),max(end_date)) [year] from table1 a,table2 b
where a.code=b.code
and end_date<='2003-10-20'
group by a.code,a.name

[zjcxc]

--测试

--测试数据
create table table1(code varchar(20) primary key,name varchar(50),remark varchar(100))
insert table1(code,name)
select 'Tg-001','张三'
union all select 'Tg-002','李四'
union all select 'Tg-003','王五'
union all select 'Tg-004','小何'
union all select 'Tg-005','小红'
union all select 'Tg-006','小张'

create table table2(code varchar(20),Lines numeric(10),start_date datetime,
years integer,end_date datetime)
insert table2(Code,Lines,start_date,years,end_date)
select 'Tg-001',1,'2002-11-21',1,'2003-11-21' --(此用户第一次交到2003-11-21)
union all select 'Tg-001',2,'2003-11-21',1,'2004-11-21' --(此用户第二次交到2004-11-21)
union all select 'Tg-002',1,'1998-03-01',1,'1999-03-01'
union all select 'Tg-003',1,'1998-02-01',2,'2000-02-01'
union all select 'Tg-003',2,'2000-02-01',8,'2008-02-01' --(此用户第二次交到2008-02-01)
union all select 'Tg-004',1,'1995-01-01',2,'1997-01-01'
union all select 'Tg-005',1,'2003-07-08',1,'2004-07-08'
union all select 'Tg-006',1,'1997-02-01',2,'1999-02-01'
union all select 'Tg-006',2,'1999-02-01',1,'2000-02-01'
union all select 'Tg-006',3,'2000-02-01',1,'2002-02-01'
go

--查询
declare @dt datetime
set @dt='2003-10-20'

select a.code,a.name,b.Start_Date,b.years,b.end_date
from table1 a
join table2 b on a.code=b.code
join(
select code,Lines=max(Lines)
from table2 a
group by code
having max(end_date)<@dt
)c on b.code=c.code and b.Lines=c.Lines
go

--删除测试
drop table table1,table2

/*--测试结果
code name Start_Date years end_date
------------ -------- -------------------------- ---- ------------------------
Tg-002 李四 1998-03-01 00:00:00.000 1 1999-03-01 00:00:00.000
Tg-004 小何 1995-01-01 00:00:00.000 2 1997-01-01 00:00:00.000
Tg-006 小张 2000-02-01 00:00:00.000 1 2002-02-01 00:00:00.000

（所影响的行数为 3 行）

--*/

[zjcxc]

--改善一下日期格式

--查询
declare @dt datetime
set @dt='2003-10-20'

select a.code,a.name
,Start_Date=convert(char(10),b.Start_Date,120)
,years
,end_date=convert(char(10),b.end_date,120)
from table1 a
join table2 b on a.code=b.code
join(
select code,Lines=max(Lines)
from table2 a
group by code
having max(end_date)<@dt
)c on b.code=c.code and b.Lines=c.Lines

[zjcxc]

--测试

--测试数据
create table table1(code varchar(20) primary key,name varchar(50),remark varchar(100))
insert table1(code,name)
select 'Tg-001','张三'
union all select 'Tg-002','李四'
union all select 'Tg-003','王五'
union all select 'Tg-004','小何'
union all select 'Tg-005','小红'
union all select 'Tg-006','小张'

create table table2(code varchar(20),Lines numeric(10),start_date datetime,
years integer,end_date datetime)
insert table2(Code,Lines,start_date,years,end_date)
select 'Tg-001',1,'2002-11-21',1,'2003-11-21' --(此用户第一次交到2003-11-21)
union all select 'Tg-001',2,'2003-11-21',1,'2004-11-21' --(此用户第二次交到2004-11-21)
union all select 'Tg-002',1,'1998-03-01',1,'1999-03-01'
union all select 'Tg-003',1,'1998-02-01',2,'2000-02-01'
union all select 'Tg-003',2,'2000-02-01',8,'2008-02-01' --(此用户第二次交到2008-02-01)
union all select 'Tg-004',1,'1995-01-01',2,'1997-01-01'
union all select 'Tg-005',1,'2003-07-08',1,'2004-07-08'
union all select 'Tg-006',1,'1997-02-01',2,'1999-02-01'
union all select 'Tg-006',2,'1999-02-01',1,'2000-02-01'
union all select 'Tg-006',3,'2000-02-01',1,'2002-02-01'
go

--查询
declare @dt datetime
set @dt='2003-10-20'

select a.code,a.name
,Start_Date=convert(char(10),b.Start_Date,120)
,years
,end_date=convert(char(10),b.end_date,120)
from table1 a
join table2 b on a.code=b.code
join(
select code,Lines=max(Lines)
from table2 a
group by code
having max(end_date)<@dt
)c on b.code=c.code and b.Lines=c.Lines
go

--删除测试
drop table table1,table2

/*--测试结果

code name Start_Date years end_date
---------- --------- ----------- ------- -----------
Tg-002 李四 1998-03-01 1 1999-03-01
Tg-004 小何 1995-01-01 2 1997-01-01
Tg-006 小张 2000-02-01 1 2002-02-01

（所影响的行数为 3 行）
--*/

[playyuer]

declare @ datetime
set @ = '2003-10-20'

select *
from Table1
where not exists (select 1
from Table2
where Code = Table1.code and end_date >= @)

select *
from Table2 a
left join Table1 on a.code = Table1.code
and not exists (select 1
from Table2
where Code = a.code and end_date >= @)


select *
from Table2 a
left join Table1 on a.code = Table1.code
where not exists (select 1
from Table2
where Code = a.code and end_date >= @)

[playyuer]

declare @ datetime
set @ = '2003-10-20'

select *
from Table1
where not exists (select 1
from Table2
where Code = Table1.code and end_date >= @)


select *
from Table2 a
left join Table1 on a.code = Table1.code
where not exists (select 1
from Table2
where Code = a.code and end_date >= @)
and a.end_date = (select max(end_date)
from Table2
where code = a.code )



select *
from Table2 a ,Table1
where a.code = Table1.code
and not exists (select 1
from Table2
where Code = a.code and end_date >= @)
and a.end_date = (select max(end_date)
from Table2
where code = a.code )

[liujiaqiang]

好，很不错，zjcxc(: 邹建 :) ( ) 谢谢你！！！！！！

[playyuer]

--faint

declare @ datetime
set @ = '2003-10-20'

select *
from Table1
where not exists (select 1
from Table2
where Code = Table1.code and end_date >= @)


select *
from Table2 a
left join Table1 on a.code = Table1.code
where not exists (select 1
from Table2
where Code = a.code and end_date >= @)
and a.end_date = (select max(end_date)
from Table2
where code = a.code )



select *
from Table2 a ,Table1
where a.code = Table1.code
and not exists (select 1
from Table2
where Code = a.code and end_date >= @)
and a.end_date = (select max(end_date)
from Table2
where code = a.code )


select *
from Table2 a ,Table1
where a.code = Table1.code
and not exists (select 1
from Table2
where Code = a.code
and end_date >= @
)
and not exists (select 1
from Table2
where code = a.code and end_date > a.end_date )




select *
from Table2 a ,Table1
where a.code = Table1.code
and not exists (select 1
from Table2
where Code = a.code
and end_date >= @
)
and not exists (select 1
from Table2
where code = a.code and end_date > a.end_date )

[liujiaqiang]

playyuer(双规干部) ( )
真的对不起，我向你表示深深的谦意。
真的对不起.

[playyuer]

别!
我的答案应该相对好理解一些!

[playyuer]

饿死事小,失节事大!