A表的ID是uniqueidentifier类型的
表A
ID NAME
C-73-A a
9-B8-4 b
5-45-D c
表B
ID A_ID TEXT
1 9-B8-4 AA
2 9-B8-4 BB
3 9-B8-4 AB
4 C-73-A CCC
5 C-73-A BBD 查询得到
NAME TEXT
a BBD
b AB
c NULL
表B的A_ID是表A的ID,表A是主表,查询后得到表A的NAME和表B的最大ID的TEXT
表A
ID NAME
C-73-A a
9-B8-4 b
5-45-D c
表B
ID A_ID TEXT
1 9-B8-4 AA
2 9-B8-4 BB
3 9-B8-4 AB
4 C-73-A CCC
5 C-73-A BBD 查询得到
NAME TEXT
a BBD
b AB
c NULL
表B的A_ID是表A的ID,表A是主表,查询后得到表A的NAME和表B的最大ID的TEXT
(
ID nvarchar(50),
[NAME] nvarchar(50)
)
insert A select 'C-73-A','a'
insert A select '9-B8-4','b'
insert A select '5-45-D','c'
create table B
(
ID int,
A_ID nvarchar(50),
[TEXT] nvarchar(50),
)
insert B select 1,'9-B8-4','AA'
insert B select 2,'9-B8-4','BB'
insert B select 3,'9-B8-4','AB'
insert B select 4,'C-73-A','CCC'
insert B select 5,'C-73-A','BBD'SELECT A.[NAME],B.[TEXT] FROM A LEFT JOIN B ON A.ID = B.A_ID AND B.ID IN(SELECT MAX(B.ID) FROM B LEFT JOIN A ON B.A_ID = A.ID GROUP BY A.ID)DROP TABLE A,B(1 個資料列受到影響)(1 個資料列受到影響)(1 個資料列受到影響)(1 個資料列受到影響)(1 個資料列受到影響)(1 個資料列受到影響)(1 個資料列受到影響)(1 個資料列受到影響)
NAME TEXT
-------------------------------------------------- --------------------------------------------------
a BBD
b AB
c NULL(3 個資料列受到影響)
insert A select 'C-73-A','a'
insert A select '9-B8-4','b'
insert A select '5-45-D','c'
create table B(ID int,A_ID nvarchar(50),[TEXT] nvarchar(50))
insert B select 1,'9-B8-4','AA'
insert B select 2,'9-B8-4','BB'
insert B select 3,'9-B8-4','AB'
insert B select 4,'C-73-A','CCC'
insert B select 5,'C-73-A','BBD'
go--1
select a.name , m.text from a left join
(select t.* from b t where id = (select max(id) from b where a_id = t.a_id)) m
on a.id = m.a_id--2
select a.name , m.text from a left join
(select t.* from b t where not exists (select 1 from b where a_id = t.a_id and id > t.id)) m
on a.id = m.a_iddrop table a , b/*
name text
-------------------------------------------------- --------------------------------------------------
a BBD
b AB
c NULL(所影响的行数为 3 行)
*/