34,837
社区成员
发帖
与我相关
我的任务
分享SQL查询某时间之前的最新记录
表1
id sn info i_date
1 1 123 2012-8-15
2 2 456 2012-8-29
3 1 345 2012-8-30
4 1 546 2012-8-26
5 2 321 2012-8-18
表2
id sn check c_date
1 1 pass 2012-8-20
2 2 fail 2012-8-31
3 1 fail 2012-8-31
4 2 pass 2012-8-20
5 1 pass 2012-8-28
需要查询的结果
sn info check i_date c_date
1 123 pass 2012-8-15 2012-8-20
2 321 pass 2012-8-18 2012-8-20
1 546 pass 2012-8-26 2012-8-28
2 456 fail 2012-8-29 2012-8-31
1 345 fail 2012-8-30 2012-8-31
结果说明：
根据ID号联合两个表显示、对应关系为表2中的每条记录对应在c_date之前且最新的表1的记录，一一对应的关系。
如：表2中第5条记录 1 pass 2012-8-28 对应的应该是表1中的第4条记录 1 546 2012-8-26
而不是在其日期之后的第3条 1 345 2012-8-30 也不是再以前的第1条 1 123 2012-8-15
麻烦问哈各位大大怎么写SQL语句？
小弟半路出家，急用！谢谢！
id sn info i_date
1 1 123 2012-8-15
2 2 456 2012-8-29
3 1 345 2012-8-30
4 1 546 2012-8-26
5 2 321 2012-8-18
表2
id sn check c_date
1 1 pass 2012-8-20
2 2 fail 2012-8-31
3 1 fail 2012-8-31
4 2 pass 2012-8-20
5 1 pass 2012-8-28
需要查询的结果
sn info check i_date c_date
1 123 pass 2012-8-15 2012-8-20
2 321 pass 2012-8-18 2012-8-20
1 546 pass 2012-8-26 2012-8-28
2 456 fail 2012-8-29 2012-8-31
1 345 fail 2012-8-30 2012-8-31
结果说明：
根据ID号联合两个表显示、对应关系为表2中的每条记录对应在c_date之前且最新的表1的记录，一一对应的关系。
如：表2中第5条记录 1 pass 2012-8-28 对应的应该是表1中的第4条记录 1 546 2012-8-26
而不是在其日期之后的第3条 1 345 2012-8-30 也不是再以前的第1条 1 123 2012-8-15
麻烦问哈各位大大怎么写SQL语句？
小弟半路出家，急用！谢谢！
...全文
请发表友善的回复…
发表回复
jackie1234556 2012-10-08
- 打赏
- 举报
[Quote=引用 1 楼 的回复:]
SQL code
declare @t1 table(id int, sn int, info int, i_date datetime)
insert into @t1
select 1, 1, 123, '2012-8-15' union all
select 2, 2, 456, '2012-8-29' union all
select 3, 1, 345, '2012-8-30' u……
[/Quote]
这个调试了，对应一条检验记录c_date还是会有多条i_date出来
SQL code
declare @t1 table(id int, sn int, info int, i_date datetime)
insert into @t1
select 1, 1, 123, '2012-8-15' union all
select 2, 2, 456, '2012-8-29' union all
select 3, 1, 345, '2012-8-30' u……
[/Quote]
这个调试了，对应一条检验记录c_date还是会有多条i_date出来
jackie1234556 2012-10-08
- 打赏
- 举报
[Quote=引用 7 楼 的回复:]
引用 6 楼 的回复:
各位大哥，有没有办法不加中间那个check来解决的啊？我这里数据是别人写的，我只负责查询。
都过去一个多月了？
不加check 是嘛意思
[/Quote]
不好意思，这个是看错了。。
引用 6 楼 的回复:
各位大哥，有没有办法不加中间那个check来解决的啊？我这里数据是别人写的，我只负责查询。
都过去一个多月了？
不加check 是嘛意思
[/Quote]
不好意思，这个是看错了。。
jackie1234556 2012-10-08
- 打赏
- 举报
[Quote=引用 3 楼 的回复:]
SQL code
declare @t1 table(id int, sn int, info int, i_date datetime)
insert into @t1
select 1, 1, 123, '2012-8-15' union all
select 2, 2, 456, '2012-8-29' union all
select 3, 1, 345, '2012-8-30' un……
[/Quote]
大哥，这个我调试时显示apply附件有语法错误
SQL code
declare @t1 table(id int, sn int, info int, i_date datetime)
insert into @t1
select 1, 1, 123, '2012-8-15' union all
select 2, 2, 456, '2012-8-29' union all
select 3, 1, 345, '2012-8-30' un……
[/Quote]
大哥，这个我调试时显示apply附件有语法错误
汤姆克鲁斯 2012-10-08
- 打赏
- 举报
[Quote=引用 6 楼 的回复:]
各位大哥，有没有办法不加中间那个check来解决的啊？我这里数据是别人写的，我只负责查询。
[/Quote]
都过去一个多月了？
不加check 是嘛意思
各位大哥，有没有办法不加中间那个check来解决的啊？我这里数据是别人写的，我只负责查询。
[/Quote]
都过去一个多月了？
不加check 是嘛意思
jackie1234556 2012-10-08
- 打赏
- 举报
各位大哥，有没有办法不加中间那个check来解决的啊？我这里数据是别人写的，我只负责查询。
以学习为目的 2012-08-31
- 打赏
- 举报
IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]
GO
CREATE TABLE [ta]([id] INT,[sn] INT,[info] INT,[i_date] DATETIME)
INSERT [ta]
SELECT 1,1,123,'2012-8-15' UNION ALL
SELECT 2,2,456,'2012-8-29' UNION ALL
SELECT 3,1,345,'2012-8-30' UNION ALL
SELECT 4,1,546,'2012-8-26' UNION ALL
SELECT 5,2,321,'2012-8-18'
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[sn] INT,[check] VARCHAR(4),[c_date] DATETIME)
INSERT [tb]
SELECT 1,1,'pass','2012-8-20' UNION ALL
SELECT 2,2,'fail','2012-8-31' UNION ALL
SELECT 3,1,'fail','2012-8-31' UNION ALL
SELECT 4,2,'pass','2012-8-20' UNION ALL
SELECT 5,1,'pass','2012-8-28'
SELECT a.[sn],
a.[info],
b.[check],
a.[i_date],
(SELECT TOP 1 [c_date]
FROM tb
WHERE [check] = b.[check]
AND [c_date] >= a.[i_date]
ORDER BY [c_date])
FROM [ta] AS a,
[tb] AS b
WHERE a.id = b.id
ORDER BY [i_date]
wu5224311 2012-08-31
- 打赏
- 举报
declare @t1 table(id int, sn int, info int, i_date datetime)
insert into @t1
select 1, 1, 123, '2012-8-15' union all
select 2, 2, 456, '2012-8-29' union all
select 3, 1, 345, '2012-8-30' union all
select 4, 1, 546, '2012-8-26' union all
select 5, 2, 321, '2012-8-18'
declare @t2 table(id int,sn int, [check] varchar(4), c_date datetime)
insert into @t2
select 1, 1, 'pass', '2012-8-20' union all
select 2, 2, 'fail', '2012-8-31' union all
select 3, 1, 'fail', '2012-8-31' union all
select 4, 2, 'pass', '2012-8-20' union all
select 5, 1, 'pass', '2012-8-28'
select a.sn,b.info,a.[check],b.i_date,a.c_date
from @t2 a cross apply(select top 1 * from @t1
where sn=a.sn and i_date<a.c_date order by i_date desc) b
order by i_date
/*
sn info check i_date c_date
----------- ----------- ----- ----------------------- -----------------------
1 123 pass 2012-08-15 00:00:00.000 2012-08-20 00:00:00.000
2 321 pass 2012-08-18 00:00:00.000 2012-08-20 00:00:00.000
1 546 pass 2012-08-26 00:00:00.000 2012-08-28 00:00:00.000
2 456 fail 2012-08-29 00:00:00.000 2012-08-31 00:00:00.000
1 345 fail 2012-08-30 00:00:00.000 2012-08-31 00:00:00.000
(5 row(s) affected)
快溜 2012-08-30
- 打赏
- 举报
declare @t1 table(id int, sn int, info int, i_date datetime)
insert into @t1
select 1, 1, 123, '2012-8-15' union all
select 2, 2, 456, '2012-8-29' union all
select 3, 1, 345, '2012-8-30' union all
select 4, 1, 546, '2012-8-26' union all
select 5, 2, 321, '2012-8-18'
declare @t2 table(id int,sn int, [check] varchar(4), c_date datetime)
insert into @t2
select 1, 1, 'pass', '2012-8-20' union all
select 2, 2, 'fail', '2012-8-31' union all
select 3, 1, 'fail', '2012-8-31' union all
select 4, 2, 'pass', '2012-8-20' union all
select 5, 1, 'pass', '2012-8-28'
select a.sn,b.info,a.[check],b.i_date,a.c_date
from @t2 a cross apply(select top 1 * from @t1
where sn=a.sn and i_date<a.c_date order by i_date desc) b
order by i_date
/*
sn info check i_date c_date
----------- ----------- ----- ----------------------- -----------------------
1 123 pass 2012-08-15 00:00:00.000 2012-08-20 00:00:00.000
2 321 pass 2012-08-18 00:00:00.000 2012-08-20 00:00:00.000
1 546 pass 2012-08-26 00:00:00.000 2012-08-28 00:00:00.000
2 456 fail 2012-08-29 00:00:00.000 2012-08-31 00:00:00.000
1 345 fail 2012-08-30 00:00:00.000 2012-08-31 00:00:00.000
(5 row(s) affected)
筱筱澄 2012-08-30
- 打赏
- 举报
--> 测试数据：[ta]
IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]
GO
CREATE TABLE [ta]([id] INT,[sn] INT,[info] INT,[i_date] DATETIME)
INSERT [ta]
SELECT 1,1,123,'2012-8-15' UNION ALL
SELECT 2,2,456,'2012-8-29' UNION ALL
SELECT 3,1,345,'2012-8-30' UNION ALL
SELECT 4,1,546,'2012-8-26' UNION ALL
SELECT 5,2,321,'2012-8-18'
--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[sn] INT,[check] VARCHAR(4),[c_date] DATETIME)
INSERT [tb]
SELECT 1,1,'pass','2012-8-20' UNION ALL
SELECT 2,2,'fail','2012-8-31' UNION ALL
SELECT 3,1,'fail','2012-8-31' UNION ALL
SELECT 4,2,'pass','2012-8-20' UNION ALL
SELECT 5,1,'pass','2012-8-28'
--------------开始查询--------------------------
SELECT a.[sn],a.[info],b .[check],a.[i_date],
(SELECT TOP 1[c_date] FROM tb WHERE [check]=b.[check] AND [c_date]>=a.[i_date] ORDER BY [c_date])
FROM [ta] as a,[tb] AS b WHERE a.id=b.id
ORDER BY [i_date]
----------------结果----------------------------
/*
sn info check i_date
----------- ----------- ----- ----------------------- -----------------------
1 123 pass 2012-08-15 00:00:00.000 2012-08-20 00:00:00.000
2 321 pass 2012-08-18 00:00:00.000 2012-08-20 00:00:00.000
1 546 pass 2012-08-26 00:00:00.000 2012-08-28 00:00:00.000
2 456 fail 2012-08-29 00:00:00.000 2012-08-31 00:00:00.000
1 345 fail 2012-08-30 00:00:00.000 2012-08-31 00:00:00.000
(5 行受影响)
*/
问苍天人生几何 2012-08-30
- 打赏
- 举报
declare @t1 table(id int, sn int, info int, i_date datetime)
insert into @t1
select 1, 1, 123, '2012-8-15' union all
select 2, 2, 456, '2012-8-29' union all
select 3, 1, 345, '2012-8-30' union all
select 4, 1, 546, '2012-8-26' union all
select 5, 2, 321, '2012-8-18'
declare @t2 table(id int,sn int, [check] varchar(4), c_date datetime)
insert into @t2
select 1, 1, 'pass', '2012-8-20' union all
select 2, 2, 'fail', '2012-8-31' union all
select 3, 1, 'fail', '2012-8-31' union all
select 4, 2, 'pass', '2012-8-20' union all
select 5, 1, 'pass', '2012-8-28'
select sn,info,[check]=(select top 1 [check] from @t2 where t.sn=sn order by abs(datediff(dd,i_date,c_date))),i_date,c_date=(select top 1 c_date from @t2 where t.sn=sn order by abs(datediff(dd,i_date,c_date))) from @t1 t
order by i_date
/*
sn info check i_date c_date
----------- ----------- ----- ----------------------- -----------------------
1 123 pass 2012-08-15 00:00:00.000 2012-08-20 00:00:00.000
2 321 pass 2012-08-18 00:00:00.000 2012-08-20 00:00:00.000
1 546 pass 2012-08-26 00:00:00.000 2012-08-28 00:00:00.000
2 456 fail 2012-08-29 00:00:00.000 2012-08-31 00:00:00.000
1 345 fail 2012-08-30 00:00:00.000 2012-08-31 00:00:00.000
*/
