一、使用第一种,发现速度慢
SELECT CategoryPermission.*,
P.LevelNum,
Flag = CAST(0 AS TINYINT) INTO #Permission FROM CategoryPermission P
OUTER APPLY
(select TOP(1) EmployeeId ,
CategoryId ,
BrandId,
RoleType from CategoryPermission WHERE EmployeeId =p.EmployeeId and CategoryId=p.CategoryId and BrandId=p.BrandId ORDER BY StartTime,[EndTime] desc)CategoryPermission;
二、改为第二种(参考文章 https://www.jb51.net/article/34655.htm)
SELECT P.*,
Flag = CAST(0 AS TINYINT) INTO #Permission from
(select CategoryPermission.* from (select n =row_number() over (partition by EmployeeId, CategoryId ,BrandId order by StartTime,EndTime desc),
EmployeeId ,
CategoryId ,
BrandId ,
LevelNum,
RoleType
from CategoryPermission )CategoryPermission
where CategoryPermission.n = 1)P