下面这个两张表关联查询的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)
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-23 05:59
社区公告
暂无公告