34,590
社区成员
发帖
与我相关
我的任务
分享
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]
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)
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)
--> 测试数据:[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 行受影响)
*/
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
*/