多表查询，去除重复ID，要求时间最大，取它的另一个编码

[qq_30890773]

两张表，业务单据数据ID 是重复的，
取审批发起时间最大的单据审批任务ID (这ID是乱码，无法使用MAX来取最大的，智能取最大时间来获取这ID)
比如
--------------------------------------------------------------------------
A表
id title
a1 a1

--------------------------------
b表
id Aid name time
b1 a1 u 2017-5-6
b2 a1 x 2017-7-8
b3 a1 z 2017-2-9

大概是这样，通过最大的时间，获取同一条下的b表id （只需要最大的时间一条），求帮助谢谢

[二月十六]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] nvarchar(22),[title] nvarchar(22))
Insert #A
select N'a1',N'a1'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([id] nvarchar(22),[Aid] nvarchar(22),[name] nvarchar(21),[time] Date)
Insert #B
select N'b1',N'a1',N'u','2017-5-6' union all
select N'b2',N'a1',N'x','2017-7-8' union all
select N'b3',N'a1',N'z','2017-2-9'
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY Aid ORDER BY time DESC ) AS num
FROM    #B 
)
SELECT  cte.id ,
        cte.Aid ,
        cte.name ,
        cte.time
FROM    cte
        JOIN #A ON #A.id = cte.Aid
WHERE   cte.num = 1;

[qq_30890773]

版主你好，是这样的现在在这基础又遇到一个问题，如果A表中有数据，但是B表中无对应的数据，怎么把A表也显示出来B表放空呢？

[qq_30890773]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] nvarchar(22),[title] nvarchar(22))
Insert #A
select N'a1',N'a1'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([id] nvarchar(22),[Aid] nvarchar(22),[name] nvarchar(21),[time] Date)
Insert #B
select N'b1',N'a1',N'u','2017-5-6' union all
select N'b2',N'a1',N'x','2017-7-8' union all
select N'b3',N'a1',N'z','2017-2-9'
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY Aid ORDER BY time DESC ) AS num
FROM    #B 
)
SELECT  cte.id ,
        cte.Aid ,
        cte.name ,
        cte.time
FROM    cte
        JOIN #A ON #A.id = cte.Aid
WHERE   cte.num = 1;

版主你好，是这样的现在在这基础又遇到一个问题，如果A表中有数据，但是B表中无对应的数据，怎么把A表也显示出来B表放空呢？

[二月十六]

是这个意思吗？

--测试数据
if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([id] nvarchar(22),[title] nvarchar(22))
Insert #A
select N'a1',N'a1' union ALL
SELECT N'b1',N'b1'
GO
if not object_id(N'Tempdb..#B') is null
    drop table #B
Go
Create table #B([id] nvarchar(22),[Aid] nvarchar(22),[name] nvarchar(21),[time] Date)
Insert #B
select N'b1',N'a1',N'u','2017-5-6' union all
select N'b2',N'a1',N'x','2017-7-8' union all
select N'b3',N'a1',N'z','2017-2-9'
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY Aid ORDER BY time DESC ) AS num
FROM    #B 
)
SELECT  #A.id ,
        cte.Aid ,
        cte.name ,
        cte.time
FROM    #A
        LEFT JOIN  cte ON #A.id = cte.Aid AND cte.num = 1

[qq_30890773]

引用 4 楼 sinat_28984567 的回复:

是这个意思吗？

--测试数据
if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([id] nvarchar(22),[title] nvarchar(22))
Insert #A
select N'a1',N'a1' union ALL
SELECT N'b1',N'b1'
GO
if not object_id(N'Tempdb..#B') is null
    drop table #B
Go
Create table #B([id] nvarchar(22),[Aid] nvarchar(22),[name] nvarchar(21),[time] Date)
Insert #B
select N'b1',N'a1',N'u','2017-5-6' union all
select N'b2',N'a1',N'x','2017-7-8' union all
select N'b3',N'a1',N'z','2017-2-9'
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY Aid ORDER BY time DESC ) AS num
FROM    #B 
)
SELECT  #A.id ,
        cte.Aid ,
        cte.name ,
        cte.time
FROM    #A
        LEFT JOIN  cte ON #A.id = cte.Aid AND cte.num = 1

多谢版主，就是这样，谢谢