select * from a inner join b on a.A_No=b.A_No where not exists(select 1 from b as t where A_No =t.A_No and B_No<t.B_No)
with t as ( select b_no, a_no, b_name, row_number() over (partition by a_no order by b_no desc) as od ) select * from a, t where a.a_no = t.a_no and t.od = 1;
---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-25 08:01:13 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[A] if object_id('[A]') is not null drop table [A] go create table [A]([A_No] int,[A_Name] nvarchar(4)) insert [A] select 1,'A1' union all select 2,'A2' --> 测试数据[B] if object_id('[B]') is not null drop table [B] go create table [B]([B_No] int,[A_No] int,[B_Name] nvarchar(4)) insert [B] select 1,1,'B1' union all select 2,1,'B2' union all select 3,2,'B3' --------------生成数据--------------------------select * from A inner JOIN (SELECT * FROM B B WHERE EXISTS (SELECT 1 FROM ( select MAX(B_NO)B_NO,A_NO from [B] GROUP BY A_NO) C WHERE B.B_NO=C.B_NO AND B.A_NO=C.A_NO)) B on a.A_No=b.A_No ----------------结果---------------------------- /* A_No A_Name B_No A_No B_Name ----------- ------ ----------- ----------- ------ 1 A1 2 1 B2 2 A2 3 2 B3*/
select * from a left join b on a.a_no=b.a_no where b.b_no in (select max(b_no) from b group by a_no)
where not exists(select 1 from b as t where A_No =t.A_No and B_No<t.B_No)
( select b_no, a_no, b_name, row_number() over (partition by a_no order by b_no desc) as od )
select * from a, t
where a.a_no = t.a_no and t.od = 1;
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-25 08:01:13
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([A_No] int,[A_Name] nvarchar(4))
insert [A]
select 1,'A1' union all
select 2,'A2'
--> 测试数据[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([B_No] int,[A_No] int,[B_Name] nvarchar(4))
insert [B]
select 1,1,'B1' union all
select 2,1,'B2' union all
select 3,2,'B3'
--------------生成数据--------------------------select *
from A inner JOIN (SELECT *
FROM B B WHERE EXISTS (SELECT 1 FROM (
select MAX(B_NO)B_NO,A_NO from [B] GROUP BY A_NO) C WHERE B.B_NO=C.B_NO AND B.A_NO=C.A_NO)) B on a.A_No=b.A_No
----------------结果----------------------------
/*
A_No A_Name B_No A_No B_Name
----------- ------ ----------- ----------- ------
1 A1 2 1 B2
2 A2 3 2 B3*/