两个表
A B
id A_name id A_id B_name
1 aaa 1 1 zzzz
2 bbb 2 1 yyyy
3 ccc 3 2 xxxx
4 2 wwww
5 3 vvvv
6 3 uuuu
A表中的id 和 B表中A_id 是关联的
求一条查询语句 显示出这样的效果A.id A.A_name B.id B.B_name
1 aaa 2 yyyy
2 bbb 4 wwww
3 ccc 6 uuuu选取B表中满足关联条件的一条记录,B表id倒序
A B
id A_name id A_id B_name
1 aaa 1 1 zzzz
2 bbb 2 1 yyyy
3 ccc 3 2 xxxx
4 2 wwww
5 3 vvvv
6 3 uuuu
A表中的id 和 B表中A_id 是关联的
求一条查询语句 显示出这样的效果A.id A.A_name B.id B.B_name
1 aaa 2 yyyy
2 bbb 4 wwww
3 ccc 6 uuuu选取B表中满足关联条件的一条记录,B表id倒序
select
*
from
a
join
b c on a.ID=c.A_ID
where
not exists(select 1 from b where A_ID=c.A_ID and id>C.ID)
select
*
from
a
join
b c on a.ID=c.A_ID
where
c.ID=(select max(ID) from b where A_ID=c.A_ID )
(
select t.* from b t where id = (select max(id) from b where a_id = t.a_id)
) m
where a.id = m.id
insert into A values(1 , 'aaa')
insert into A values(2 , 'bbb')
insert into A values(3 , 'ccc')
create table B(id int, A_id int, B_name varchar(10))
insert into B values(1, 1, 'zzzz')
insert into B values(2, 1, 'yyyy')
insert into B values(3, 2, 'xxxx')
insert into B values(4, 2, 'wwww')
insert into B values(5, 3, 'vvvv')
insert into B values(6, 3, 'uuuu')
goselect a.* , m.id , m.b_name from A,
(
select t.* from b t where id = (select max(id) from b where a_id = t.a_id)
) m
where a.id = m.a_iddrop table A,B/*
id A_name id b_name
----------- ---------- ----------- ----------
1 aaa 2 yyyy
2 bbb 4 wwww
3 ccc 6 uuuu(所影响的行数为 3 行)*/
create table A
(
id int,
A_name nvarchar(10)
)
insert A select 1,'aaa'
insert A select 2,'bbb'
insert A select 3,'ccc'
create table B
(
id int,
A_id int,
B_name nvarchar(10)
)
insert B select 1,1,'zzzz'
insert B select 2, 1, 'yyyy'
insert B select 3, 2, 'xxxx'
insert B select 4, 2, 'wwww'
insert B select 5, 3, 'vvvv'
insert B select 6, 3, 'uuuu' select * from A
left join
(
select * from B as tmp
where not exists(select * from B where tmp.A_id=A_id and id>tmp.id)
)B on A.id=B.A_id
drop table A
if object_id('B') is not null
drop table B
go
create table A(id int,A_name varchar(10))
insert into A
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc'create table B(id int,A_id int,B_name varchar(10))
insert into B
select 1,1,'zzzz' union all
select 2,1,'yyyy' union all
select 3,2,'xxxx' union all
select 4,2,'wwww' union all
select 5,3,'vvvv' union all
select 6,3,'uuuu' select * from
(
select A.id,A.A_name,B.id as bid,B.A_id,B.B_name from A
inner join B
on A.id=B.A_id
) aa
where aa.bid in(select max(id) from B group by A_id)接分了