数据库多条记录条件判断问题

[kinglshadow]

select * from [order] where time between
(select starttime from TradeTime where [type]='nowday')
and (select endtime from TradeTime where [type]='nowday')
问题是这样的：我现在要判断一个时间time在starttime与endtime之间，而starttime,endtime又是表tradetime中的两个字段
tradetime表的字段有这几个：id,type,starttime,endtime。上面的SQL语句可以解决type='nowday'只有一条记录的情况，但是如果有多条记录就不行了。数据库返回这样的错误：子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。在此请教高人给出解决多条记录问题的SQL语句。或则有什么好的做法也推荐一下。。

[pt1314917]

select a.* from [order] a,TradeTime b
 where a.[time] between b.starttime and b.endtime
and b.[type]='nowday'

[华夏小卒]

给点记录

[icelovey]

select * from [order] 
where time between (select top 1 starttime from TradeTime where [type]='nowday' order by starttime) 
               and (select top 1 endtime from TradeTime where [type]='nowday' order by endtime desc) 


取STARTTIME最小的和ENDTIME最大的不就可以了吗?

[-狙击手-]

select * from [order] where time between
(select max(starttime) from TradeTime where [type]='nowday')
and (select max(endtime) from TradeTime where [type]='nowday')

---
select a.*
from [order] a,
(select top 1 * from tradetime where type = 'nowday') b
where a.time between b.starttime and b.endtime

[--小F--]

select * from [order] where time between starttime and endtime
直接这样呢？

[kinglshadow]

好像都不行把。。。tradetime的记录如下
1 nowday 2009-11-08 01:05:00 2009-11-25 05:05:00 0
2 mon 2009-11-08 00:00:00 2008-11-09 00:00:00 0
4 tues 2009-11-09 00:00:00 2009-11-11 00:00:00 0
5 wed 2009-11-11 00:00:00 2009-11-12 00:00:00 0
6 thur 2009-11-12 00:00:00 2009-11-13 00:00:00 0
7 fri 2009-11-13 00:00:00 2009-11-14 00:00:00 0
8 sat 2009-11-14 00:00:00 2009-11-15 00:00:00 0
9 sun 2009-11-15 00:00:00 2009-11-16 00:00:00 0
10 nowday 2009-11-15 00:00:00 2009-11-20 00:00:00 1

[kinglshadow]

就是同一个nowday 可能会有不同的时间段

[华夏小卒]

1楼。PT哥的应该可以

[忆轩辕]

这个用连接比较好。。。。。。。

select a.* from [order] a,TradeTime b
 where a.[time] between b.starttime and b.endtime
and b.[type]='nowday'

---1楼正解