sql同表分组取第一条

最新推荐文章于 2024-04-24 13:27:31 发布

葩熊

最新推荐文章于 2024-04-24 13:27:31 发布

阅读量316

点赞数

分类专栏： sql 文章标签：数据库 sql

本文链接：https://blog.csdn.net/sinat_36500530/article/details/126098358

版权

sql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、使用第一种，发现速度慢

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