sql 一对多查询最近一条

感谢 http://bbs.csdn.net/topics/391048578?page=1

create table A
(
    [Id] [uniqueidentifier] NOT NULL,
    [EventNo] [nvarchar](50) NULL,
    [EventName] [nvarchar](200) NULL
)create table B
(
    [Id] [uniqueidentifier] NOT NULL,
    [AId] [uniqueidentifier] NULL,
    [status] int,
    [EventDetail] nvarchar(500) null
)Declare @Id [uniqueidentifier]
select @Id = NEWID()
insert into A select @Id,'','test1'
insert into B select NEWID(),@Id,0,'detail10'
insert into B select NEWID(),@Id,0,'detail11'
insert into B select NEWID(),@Id,3,'detail12'select @Id = NEWID()
insert into A select @Id,'','test2'
insert into B select NEWID(),@Id,0,'detail20'
insert into B select NEWID(),@Id,3,'detail21'select @Id = NEWID()
insert into A select @Id,'','test3'select @Id = NEWID()
insert into A select @Id,'','test4'
insert into B select NEWID(),@Id,0,'detail40'
insert into B select NEWID(),@Id,1,'detail41'
insert into B select NEWID(),@Id,3,'detail42'--
select A.EventNo,A.EventName,B.EventDetail From A  left join B on a.Id = b.AId
EventNo    EventName    EventDetail
1    test1    detail10
1    test1    detail11
1    test1    detail12
2    test2    detail20
2    test2    detail21
3    test3    NULL
4    test4    detail40
4    test4    detail41
4    test4    detail42--想要实际的效果为
EventNo    EventName    EventDetail
1          test1    　　 detail10     2          test2    　　 detail20
3          test3    　　 NULL
4          test4    　　 detail40

解决办法：

--方法1
SELECT A.EventNo,A.EventName,T2.EventDetail
FROM A
    OUTER APPLY(SELECT TOP 1 EventDetail FROM B WHERE A.ID=B.AID ORDER BY status)T2--方法2
SELECT EventNo,EventName,EventDetail
FROM(
    SELECT A.EventNo,A.EventName,B.EventDetail
        ,ROW_NUMBER()OVER(PARTITION BY A.ID ORDER BY B.status)RN
    FROM A
        LEFT JOIN B ON A.ID=B.AID
    --WHERE 其他条件 加在这最好
)T
WHERE RN=1
ORDER BY EventNo

方法一已经验证，可以使用。

对应自己数据库：

现在只有一个用户视图，需要select 单位并排序，因为一个单位有多个用户

select oaa.unitid,oaa.unitname,t2.DepartmentSortIndex from (
select * from ( select distinct(o.UnitId),o.UnitName from [McsDW].[dbo].[UserViewAll_DW] o) oa) oaa
outer apply (SELECT TOP 1 DepartmentSortIndex FROM [McsDW].[dbo].[UserViewAll_DW] B WHERE oaa.UnitId=B.UnitId ORDER BY DepartmentSortIndex) t2
order by DepartmentSortIndex