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

liulang457 2010-10-23 05:59:34

表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)
)

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

...全文

51 点赞收藏 4

写回复

4 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

zsh0809 2010-10-23

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)

回复赞

dawugui 2010-10-23

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

回复赞

dawugui 2010-10-23

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 2010-10-23

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)

回复赞