下面这个两张表关联查询的SQL怎么写？

[liulang457]

表A
a_id name
1 A1
2 A2
3 A3

表B
b_id value a_id
1 0.1 1
2 0.2 1
3 0.3 1
4 0.2 2
5 0.4 2
查询出A.*，B.*。条件是：表B中b_id最小且value值从0到0.3之间的记录。
得出结果应该是：
a_id name b_id value a_id
1 A1 1 0.1 1
2 A2 4 0.2 2

我的语句是这样的：

SELECT a.*，b.*
FROM A a
WHERE a.a_id IN(
SELECT b.a_id
FROM B b
WHERE
b.value > 0
AND b.value < 0.3
AND b.b_id = (SELECT MIN(bb.b_id) FROM B bb WHERE bb.a_id = a.a_id)
)

请大家指教有没有问题，或者有没有效率更高一点的方式

[dawugui]

select a.* , t.* from a , b t where a.a_id = t.a_id and t.value between 0 and 0.3 and not exists (select 1 from b where a_id = t.a_id and value < t.value)

[dawugui]

create table A(a_id int,name varchar(10))
insert into a values(1 ,'A1')
insert into a values(2 ,'A2')
insert into a values(3 ,'A3')
create table B(b_id int,value decimal(18,1) ,a_id int)
insert into b values(1, 0.1 ,1)
insert into b values(2, 0.2 ,1)
insert into b values(3, 0.3 ,1)
insert into b values(4, 0.2 ,2)
insert into b values(5, 0.4 ,2)
go

select a.* , t.* from a , b t where a.a_id = t.a_id and t.value between 0 and 0.3 and not exists (select 1 from b where a_id = t.a_id and value < t.value and value between 0 and 0.3)

drop table a , b

/*
a_id        name       b_id        value                a_id        
----------- ---------- ----------- -------------------- ----------- 
1           A1         1           .1                   1
2           A2         4           .2                   2

（所影响的行数为 2 行）
*/

[dawugui]

create table A(a_id int,name varchar(10))
insert into a values(1 ,'A1')
insert into a values(2 ,'A2')
insert into a values(3 ,'A3')
create table B(b_id int,value decimal(18,1) ,a_id int)
insert into b values(1, 0.1 ,1)
insert into b values(2, 0.2 ,1)
insert into b values(3, 0.3 ,1)
insert into b values(4, 0.2 ,2)
insert into b values(5, 0.4 ,2)
go

select a.* , t.* from a , b t where a.a_id = t.a_id and t.value between 0 and 0.3 and not exists (select 1 from b where a_id = t.a_id and value < t.value and value between 0 and 0.3)
/*
a_id        name       b_id        value                a_id        
----------- ---------- ----------- -------------------- ----------- 
1           A1         1           .1                   1
2           A2         4           .2                   2

（所影响的行数为 2 行）
*/
select a.* , t.* from a , b t where a.a_id = t.a_id and t.value between 0 and 0.3 and value = (select min(value) from b where a_id = t.a_id and value between 0 and 0.3)
/*
a_id        name       b_id        value                a_id        
----------- ---------- ----------- -------------------- ----------- 
1           A1         1           .1                   1
2           A2         4           .2                   2

（所影响的行数为 2 行）
*/
drop table a , b

[「已注销」]

SELECT * FROM 
#TB t
LEFT JOIN 
(
	SELECT * FROM #TC C
	WHERE NOT EXISTS(SELECT NULL FROM #TC WHERE C.a_id=a_id AND C.b_id>B_ID)
)M
ON m.a_id=t.a_id
WHERE m.[value] BETWEEN 0 AND 0.3

a_id        name       b_id        value    a_id
----------- -------------- -------------------------
1           A1         1           0.1        1
2           A2         4           0.2        2

(2 row(s) affected)