问题是这样的：
现在有两张表 A ，b
A的主键A_No 与 B 中A_No 是主外键关系
然后查询
select * from A inner join B on a.A_No=b.A_No
现在我联合查询只想得到最新的数据 也就是 不要第一条数据， 只显示B中与A表相互关联的主键的最新的那条数据
------解决方案--------------------
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)