34,590
社区成员
发帖
与我相关
我的任务
分享
select
*
from
tb a
where
not exists (select 1 from (select time=min(time) from tb where code=0 ) t where t.time>a.time)
and
code in ('0','9')
declare @tb table (id nvarchar(10),date int,code int,time int)
insert into @tb select 'A',1123,9,15
union all select 'A',1123,0,1535
union all select 'A',1123,9,1620
union all select 'A',1124,0,2330
union all select 'A',1124,9,0010
select * from @tb a where not exists
(select 1 from (select time=min(time) from @tb where code=0 ) t where t.time>a.time)
/*
(5 行受影响)
id date code time
---------- ----------- ----------- -----------
A 1123 0 1535
A 1123 9 1620
A 1124 0 2330
(3 行受影响)
*/
最优的等楼下
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/26
-- Version: SQL SERVER 2005
-- =============================================
declare @TB table([ID] varchar(1),[DATE] int,[CODE] int,[TIME] varchar(4))
insert @TB
select 'A',1123,9,'0015' union all
select 'A',1123,0,'1535' union all
select 'A',1123,9,'1620' union all
select 'A',1124,0,'2330' union all
select 'A',1124,9,'0010'
select *
from @TB a
where (code=0)
or (code=9 and [TIME]>(select min(time) from @tb where code=0))
--测试结果:
/*
ID DATE CODE TIME
---- ----------- ----------- ----
A 1123 0 1535
A 1123 9 1620
A 1124 0 2330
(3 row(s) affected)
*/
--> 生成测试数据: @tb
DECLARE @tb TABLE (ID varchar(1),DATE int,CODE int,TIME varchar(4))
INSERT INTO @tb
SELECT 'A',1123,9,'0015' UNION ALL
SELECT 'A',1123,0,'1535' UNION ALL
SELECT 'A',1123,9,'1620' UNION ALL
SELECT 'A',1124,0,'2330' UNION ALL
SELECT 'A',1124,9,'2340' UNION ALL
SELECT 'A',1124,0,'2356' UNION ALL
SELECT 'A',1124,9,'2358' UNION ALL
SELECT 'B',1123,0,'0705' UNION ALL
SELECT 'B',1123,9,'1706' UNION ALL
SELECT 'B',1124,0,'0710' UNION ALL
SELECT 'B',1124,9,'0806' UNION ALL
SELECT 'B',1124,0,'1659' UNION ALL
SELECT 'B',1124,9,'1706'
--SQL查询如下:
SELECT A.ID,A.DATE,A.MIN_TIME_0,B.TIME AS MIN_TIME_9
FROM (SELECT ID,DATE,MIN(TIME) AS MIN_TIME_0
FROM @tb WHERE CODE = 0 GROUP BY ID,DATE) AS A
JOIN @tb AS B
ON A.ID=B.ID AND A.DATE =B.DATE
AND B.CODE = 9 AND B.TIME > A.MIN_TIME_0
WHERE NOT EXISTS(
SELECT * FROM @tb
WHERE ID = B.ID AND DATE = B.DATE
AND CODE = 9 AND TIME < B.TIME
AND TIME > A.MIN_TIME_0
)
ORDER BY 1,2;
/*
ID DATE MIN_TIME_0 MIN_TIME_9
---- ----------- ---------- ----------
A 1123 1535 1620
A 1124 2330 2340
B 1123 0705 1706
B 1124 0710 0806
(4 行受影响)
*/
--> 生成测试数据: @tb
DECLARE @tb TABLE (ID varchar(1),DATE int,CODE int,TIME varchar(4))
INSERT INTO @tb
SELECT 'A',1123,9,'0015' UNION ALL
SELECT 'A',1123,0,'1535' UNION ALL
SELECT 'A',1123,9,'1620' UNION ALL
SELECT 'A',1124,0,'2330' UNION ALL
SELECT 'A',1124,9,'2340' UNION ALL
SELECT 'A',1124,0,'2356' UNION ALL
SELECT 'A',1124,9,'2358' UNION ALL
SELECT 'B',1123,0,'0705' UNION ALL
SELECT 'B',1123,9,'1706' UNION ALL
SELECT 'B',1124,0,'0710' UNION ALL
SELECT 'B',1124,9,'0806' UNION ALL
SELECT 'B',1124,0,'1659' UNION ALL
SELECT 'B',1124,9,'1706'
--SQL查询如下:
SELECT *
FROM (SELECT ID,DATE,MIN(TIME) AS MIN_TIME_0
FROM @tb WHERE CODE= 0 GROUP BY ID,DATE) AS T
OUTER APPLY (SELECT MIN(TIME) AS MIN_TIME_9 FROM @tb
WHERE ID = T.ID AND DATE = T.DATE
AND CODE = 9 AND TIME > T.MIN_TIME_0) AS B
ORDER BY 1,2
/*
ID DATE MIN_TIME_0 MIN_TIME_9
---- ----------- ---------- ----------
A 1123 1535 1620
A 1124 2330 2340
B 1123 0705 1706
B 1124 0710 0806
(4 行受影响)
*/