小弟有两个表做比对,需要用到时间段查询
a表中日期字段用的datetime类型,而b表中用的是字符型的,表大致结构如下:a表
交易号 日期
001 2009-07-02 00:00:00 093
....b表
交易号 日期
001 20090702
....我想求的结果是
对ab表中 2009-07-02 至 2009-07-04之间的数据进行比对
输入的两个条件如上,是字符型的
求SQL语句
要求:
1、求AB表中相同时间段内交易号相同的记录
2、能够实现 输入 2009-07-02 至 2009-07-02 就只查询出 2009-07-02一天的,如果输入 2009-07-02 至 2009-07-03 就查询出2号和3号两天的谢谢高手指点
a表中日期字段用的datetime类型,而b表中用的是字符型的,表大致结构如下:a表
交易号 日期
001 2009-07-02 00:00:00 093
....b表
交易号 日期
001 20090702
....我想求的结果是
对ab表中 2009-07-02 至 2009-07-04之间的数据进行比对
输入的两个条件如上,是字符型的
求SQL语句
要求:
1、求AB表中相同时间段内交易号相同的记录
2、能够实现 输入 2009-07-02 至 2009-07-02 就只查询出 2009-07-02一天的,如果输入 2009-07-02 至 2009-07-03 就查询出2号和3号两天的谢谢高手指点
set @begintime='2009-7-11'
set @endtime='2009-7-11'
select a.交易号
from a join b on CONVERT(varchar(10),a.日期,120)=LEFT(b.日期,4)+'-'+SUBSTRING(b.日期,5,2)+'-'+RIGHT(b.日期,2)
where CONVERT(varchar(10),a.日期,120) between @begintime and @endtime
select @start_date = '2009-07-02',@end_date = '2009-07-04';select * from tb1 as a
join tb2 as b
on a.交易号 = b.交易号
and a.日期=cast(b.日期 as datetime)
where a.日期 >= @start_date and a.日期 < dateadd(day,1,@end_date);
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('a') IS NOT NULL
DROP TABLE a
GO
CREATE TABLE a(交易号 varchar(10),日期 datetime)
go
insert a SELECT '001','2009-07-11'
UNION ALL SELECT '001','2009-07-12'
UNION ALL SELECT '001','2009-07-13'
UNION ALL SELECT '002','2009-07-11'
UNION ALL SELECT '002','2009-07-14'
go
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE b(交易号 varchar(10),日期 varchar(10))
go
insert b SELECT '001','20090711'
UNION ALL SELECT '001','20090712'
UNION ALL SELECT '002','20090711'
UNION ALL SELECT '002','20090712'
UNION ALL SELECT '002','20090714'
godeclare @begintime varchar(10),@endtime varchar(10)
set @begintime='2009-7-11'
set @endtime='2009-7-12'
select a.交易号,日期=convert(varchar(10),a.日期,120)
from a join b on CONVERT(varchar(10),a.日期,120)=LEFT(b.日期,4)+'-'+SUBSTRING(b.日期,5,2)+'-'+RIGHT(b.日期,2)
and a.交易号=b.交易号
where a.日期 between cast(@begintime AS datetime) and cast (@endtime as datetime)/*------------
(5 行受影响)
交易号 日期
---------- ----------
001 2009-07-11
001 2009-07-12
002 2009-07-11
-------*/
select @start_date = '2009-07-02',@end_date = '2009-07-04';select * from tb1 as a
join tb2 as b
on a.交易号 = b.交易号
and CONVERT(VARCHAR(8),a.日期,112)=b.日期
where a.日期 >= @start_date and a.日期 < dateadd(day,1,@end_date);
DROP TABLE a
GO
CREATE TABLE a(交易号 varchar(10),日期 datetime)
go
insert a SELECT '001','2009-07-11'
UNION ALL SELECT '001','2009-07-12'
UNION ALL SELECT '001','2009-07-13'
UNION ALL SELECT '002','2009-07-11'
UNION ALL SELECT '002','2009-07-14'
go
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE b(交易号 varchar(10),日期 varchar(10))
go
insert b SELECT '001','20090711'
UNION ALL SELECT '001','20090712'
UNION ALL SELECT '002','20090711'
UNION ALL SELECT '002','20090712'
UNION ALL SELECT '002','20090714'
goIF OBJECT_ID('sp') IS NOT NULL
DROP proc sp
GO
create proc sp
@begintime varchar(10),@endtime varchar(10)
as
select a.交易号,a.日期
from a
join b
on CONVERT(varchar,a.日期,23)=cast(b.日期 as datetime)
and a.交易号=b.交易号
where a.日期 between @begintime and @endtime
go--调用查询:
exec sp '2009-7-11','2009-7-11'
/*
交易号 日期
---------- -----------------------
001 2009-07-11 00:00:00.000
002 2009-07-11 00:00:00.000(2 行受影响)
*/
exec sp '2009-7-11','2009-7-13'
/*
交易号 日期
---------- -----------------------
001 2009-07-11 00:00:00.000
001 2009-07-12 00:00:00.000
002 2009-07-11 00:00:00.000(3 行受影响)
*/
借用上面的数据.
go
insert a SELECT '001','2009-07-11'
UNION ALL SELECT '001','2009-07-12'
UNION ALL SELECT '001','2009-07-13'
UNION ALL SELECT '002','2009-07-11'
UNION ALL SELECT '002','2009-07-14'
go
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE b(交易号 varchar(10),日期 varchar(10))
go
insert b SELECT '001','20090711'
UNION ALL SELECT '001','20090712'
UNION ALL SELECT '002','20090711'
UNION ALL SELECT '002','20090712'
UNION ALL SELECT '002','20090714'
go
declare @start_date datetime,@end_date datetime;
select @start_date = '2009-07-09',@end_date = '2009-07-12';select * from A as T1
join B as T2
on T1.交易号 = T2.交易号
and CONVERT(VARCHAR(8),T1.日期,112)=T2.日期
where T1.日期 >= @start_date and T1.日期 < dateadd(day,1,@end_date);交易号 日期 交易号 日期
---------- ------------------------------------------------------ ---------- ----------
001 2009-07-11 00:00:00.000 001 20090711
001 2009-07-12 00:00:00.000 001 20090712
002 2009-07-11 00:00:00.000 002 20090711(所影响的行数为 3 行)借迪哥数据测试一下
where convert(varchar(10,),a.日期,112) between '2009-07-02' and '2009-07-02'
仅供参考。。