22,301
社区成员




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)
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
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 行)
*/
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)