双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

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

2004-03-26 09:48:31 81 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

liujiaqiang 墨衍会员 · AI 创作全网分发关注 + 加入社区

SQL语句总结建表语句 CREATE TABLE students (sno VARCHAR(3) NOT NULL, sname VARCHAR(4) NOT NULL, ssex VARCHAR(2) NOT NULL, sbirthday DATETIME, class VARCHAR(5)) CREATE TABLE courses (cno VARCHAR(5) NOT NULL,... 阅读详情

请高手帮忙，如何写一条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

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

...全文

MLX90640 热成像 STM32 MLX90640 热成像阅读详情

liujiaqiang

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

playyuer 2004-03-28

打赏
举报

饿死事小,失节事大!

playyuer 2004-03-26

打赏
举报

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

liujiaqiang 2004-03-26

打赏
举报

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

playyuer 2004-03-26

打赏
举报

--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 2004-03-26

打赏
举报

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

playyuer 2004-03-26

打赏
举报

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 )

playyuer 2004-03-26

打赏
举报

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 >= @)

zjcxc 元老 2004-03-26

打赏
举报

--测试

--测试数据
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 行）
--*/

zjcxc 元老 2004-03-26

打赏
举报

--改善一下日期格式

--查询
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 元老 2004-03-26

打赏
举报

--测试

--测试数据
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 行）

--*/

orcale 2004-03-26

打赏
举报

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 元老 2004-03-26

打赏
举报

--查询
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

welyngj 2004-03-26

打赏
举报

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)

✦双11狂欢大促✦

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

相关推荐

双11狂欢大促