ID DATE CODE TIME
=============================
A 1123 9 0015
A 1123 0 1535
A 1123 9 1620A 1124 0 2330
A 1124 9 0010针对以上鼠标请教一个SQL,查询出:11月23日 1. CODE=0
2. CODE=9中TIME大于CODE=0中最小TIME的所有记录在线等!
=============================
A 1123 9 0015
A 1123 0 1535
A 1123 9 1620A 1124 0 2330
A 1124 9 0010针对以上鼠标请教一个SQL,查询出:11月23日 1. CODE=0
2. CODE=9中TIME大于CODE=0中最小TIME的所有记录在线等!
SELECT * FROM TB WHERE CODE=0 AND DATE ='1123'
2.select * from table where code=9 and time>(select min(time) from table where code=0)
2.select * from table where code=9 and data='1123' and time>(select min(time) from table where code=0 and data='1123')
-- Author: T.O.P
-- Create date: 2009/11/26
-- Version: SQL SERVER 2005
-- =============================================
declare @TB1 table([ID] varchar(1),[DATE] int,[CODE] int,[TIME] varchar(4))
insert @TB1
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 @TB1 WHERE CODE=0select *
from @TB1 A
WHERE CODE = 9
AND NOT EXISTS(SELECT 1 FROM @TB1 WHERE [CODE]=0 AND A.ID = ID AND A.DATE=DATE AND A.TIME<TIME)--测试结果:
/*
ID DATE CODE TIME
---- ----------- ----------- ----
A 1123 0 1535
A 1124 0 2330(2 row(s) affected)ID DATE CODE TIME
---- ----------- ----------- ----
A 1123 9 1620(1 row(s) affected)
*/
2.select * from table where code=9 and data='1123' and time>(select min(time) from table where code=0 and data='1123')
用这个吧,肯定对.
select
*
from
tb
where
code=0 and data='1123'
2.
select
*
from
tb
where
code=9
and
data='1123' and time>(select min(time) from tb where code=0 and data='1123')
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(5) ,[date] varchar(10),[code] varchar(10),[time] varchar(10))
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','1123','9','1630' union all
select 'A','1124','0','2330' union all
select 'A','1124','9','0015'
select * from tb where code=0 and [date]='1123'select top 1 * from tb where code=9 and [date]='1123' and [time]>(select [time] from tb where code=0 and [date]='1123') order by [time] asc
select top 1 * from tb where code=9 and [date]='1123' and [time]>(select min([time]) from tb where code=0 and [date]='1123') order by [time] asc
少了个函数!