求SQL语句！

[VBDN]

表A中有一字段B，可能的值为0-999之间的整数。
操作1：100-300的数中最小的数：SELECT MIN(B) FROM A WHERE B BETWEEN 100 AND 300
操作2：700-900的数中最小的数：SELECT MIN(B) FROM A WHERE B BETWEEN 700 AND 900
操作3：300-700的数中最小的数：SELECT MIN(B) FROM A WHERE B BETWEEN 300 AND 700
求一数：
如果300-700之间的数存在，则得到操作3的结果;
如果300-700之间的数不存在，而100-300之间的数存在，则得到操作1的结果；
如果300-700和100-300之间的数都不存在，而存在700-900之间的数，则得到操作2的结果；
如果100-900之间的数都不存在，则什么也不返回。
既取数优先顺序为：操作3 --> 操作1 --> 操作2
（注：数据库为ACCESS97，前端开发环境为VB6，采用ADO技术，JET4.1引擎）。

[ccmoon]

IF EXISTS(
SELECT MIN(B)
FROM A
WHERE B BETWEEN 300 AND 700)

SELECT MIN(B)
FROM A
WHERE B BETWEEN 300 AND 700

ELSE
IF EXISTS(
SELECT MIN(B)
FROM A WHERE B
BETWEEN 100 AND 300
)

SELECT MIN(B)
FROM A WHERE B
BETWEEN 100 AND 300

ELSE
IF EXISTS(
SELECT MIN(B)
FROM A WHERE B
BETWEEN 700 AND 900
)

SELECT MIN(B)
FROM A WHERE B
BETWEEN 700 AND 900

[victorycyz]

select top 1 minb as b
from (
SELECT MIN(B) as minB,2 as r FROM A WHERE B BETWEEN 100 AND 300
union
SELECT MIN(B),3 FROM A WHERE B BETWEEN 700 AND 900
union
SELECT MIN(B),1 from A WHERE B BETWEEN 300 AND 700
) a
order by r

[realgz]

楼上的意思是：

select top 1 minb as b
from (

SELECT MIN(B) from A WHERE B BETWEEN 300 AND 700
union all
SELECT MIN(B) FROM A WHERE B BETWEEN 100 AND 300
union all
SELECT MIN(B) FROM A WHERE B BETWEEN 700 AND 900

) a


吧

[realgz]

select top 1 min_b as b
from (

SELECT MIN(B) min_b from A WHERE B BETWEEN 300 AND 700
union all
SELECT MIN(B) FROM A WHERE B BETWEEN 100 AND 300
union all
SELECT MIN(B) FROM A WHERE B BETWEEN 700 AND 900

) a

[realgz]

select top 1 min_b as b
from (

SELECT MIN(B) min_b from A WHERE B BETWEEN 300 AND 700
union all
SELECT MIN(B) FROM A WHERE B BETWEEN 100 AND 300
union all
SELECT MIN(B) FROM A WHERE B BETWEEN 700 AND 900

) a
where min_b is not null

[victorycyz]

是了，realgz改进的就是我想做到的了。

[playyuer]

select min(min_b) as b
from
(
SELECT MIN(B) as min_b,1 as f
from A WHERE B BETWEEN 300 AND 700
union
SELECT MIN(B),2
FROM A WHERE B BETWEEN 100 AND 300
union
SELECT MIN(B),3
FROM A WHERE B BETWEEN 700 AND 900
) a
where min_b is not null

[winternet]

up