表A
id info
1 aa
2 cc
3 dd
表B
id aid edit
1 1 2
2 1 3
3 2 1
4 1 4
5 2 3
6 3 2
期望结果
id info edit
1 aa 4
2 cc 3
3 dd 2
表A[id]和表B[aid]对应，按表A取数据，并取出对应的edit，edit取最后一次操作，就按表B的id排序。
请问SQL语句怎么写？
------解决方案--------------------
- SQL code
select a.id, a.info, b.editfrom ta a,tb b where a.id=b.aidand b.id=(select max(Id) from tb where aid=b.aid)
------解决方案--------------------
- SQL code
select a.id, a.info, b.editfrom a inner join b on a.id=b.aidwhere b.id=(select max(Id) from b group by aid)
------解决方案--------------------
select
A.id,B.aid,B.edit
from A
inner join B on B.aid = A.id
where B.id in (select max(id) from B group by aid)
------解决方案--------------------
- SQL code
select a.id,a.info,b.editfrom a,b twhere a.id=t.bidand not exists(select 1 from b where aid=t.aid and id>t.id)
------解决方案--------------------
- SQL code
--表A--id info--1 aa--2 cc--3 dd--表B--id aid edit --1 1 2 --2 1 3--3 2 1--4 1 4--5 2 3--6 3 2 if OBJECT_ID('表A')is not null drop table 表A go create table 表A (id int, info varchar(20)) insert into 表A values(1, 'aa') insert into 表A values(2, 'cc') insert into 表A values(3, 'dd') if OBJECT_ID('表B')is not null drop table 表B go create table 表B (id int,aid int,edit INT)insert into 表B values( 1, 1 ,2 )insert into 表B values(2, 1 ,3 )insert into 表B values(3 ,2, 1)insert into 表B values( 4 ,1, 4)insert into 表B values(5, 2, 3 )insert into 表B values(6 ,3 ,2 )--期望结果--id info edit--1 aa 4--2 cc 3--3 dd 2select a.*,(select max(edit) from 表B where a.id=aid )edit from 表A a id info edit----------- -------------------- -----------1 aa 42 cc 33 dd 2(3 行受影响)
------解决方案--------------------
select A.id,A.info,B.edit
from A,B
where A.id=B.aid
and B.id in(select max(id)
from b
group by b.id)