下面这个两张表关联查询的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)
)

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

...全文
73 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
「已注销」 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)

22,301

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧