求SQL(送分）

[Jephen]

ID DATE CODE TIME
=============================
A 1123 9 0015
A 1123 0 1535
A 1123 9 1620

A 1124 0 2330
A 1124 9 0010

针对以上鼠标请教一个SQL，查询出：

11月23日 1. CODE=0
2. CODE=9中TIME大于CODE=0中最小TIME的所有记录

在线等！

[liangCK]

先接分.

[SQL77]

1
SELECT * FROM TB WHERE CODE=0 AND DATE    ='1123'

[wtnu200]

1.select * from table where code=0
2.select * from table where code=9 and time>(select min(time) from table where code=0)

[wtnu200]

1.select * from table where code=0 and data='1123'
2.select * from table where code=9 and data='1123' and time>(select min(time) from table where code=0 and data='1123')

[icelovey]

-- =============================================
-- 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=0

select * 
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)


*/

[wyfni]

#4楼 得分：0回复于：2009-11-26 10:19:001.select * from table where code=0 and data='1123'
2.select * from table where code=9 and data='1123' and time>(select min(time) from table where code=0 and data='1123')

用这个吧,肯定对.

[--小F--]

1.
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')

[lidanzi]

date time 为什么不合在一起？

[zhangle861010]

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

[Jephen]

对不起，我的表达不太好，我想查询出同时满足那两个条件，查询最快的办法，因为这个表特别大，应该减少对他啊的引用

[zhangle861010]

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
少了个函数！