再看case when 在row_number中的使用

最新推荐文章于 2024-01-03 16:08:10 发布

王翼鹏

最新推荐文章于 2024-01-03 16:08:10 发布

阅读量4.3k

点赞数

分类专栏： sql server 文章标签： sql server

本文链接：https://blog.csdn.net/zghnpdswyp/article/details/53634584

版权

sql server 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

SELECT   tpbt.PMName,
                                       k.[ActivityName],
                                       k.[ActionerName],
                                       k.[Data],
                                       tpae.PMStatus,
                                       tpae.FormID,
                                       tpae.BizID,
                                       tpae.AEID,
                                       tpbt.TempID,
                                       tpbt.FlowStatus,
                                      case when te.UserName is null then '' else  te.UserName+'('+te.UserCode+')' end AS CurrentUserName,
                                       ROW_NUMBER() OVER(
                                            ORDER BY(
                                                CASE WHEN  k.[ActionerName]='K2:CORP\'+@useraccount THEN   DATEADD(yy, 100, tpbt.CreateOn)
                                                    
                                                     ELSE  tpbt.CreateOn
                                                END
                                            ) DESC
                                        )                         AS [Index]
                                FROM   T_PM_AssessEmployees      AS tpae
                                       INNER JOIN T_PM_BaseTask  AS tpbt
                                            ON  tpbt.BizID = tpae.BizID
                                       left JOIN [K2].[dbo].[V_AllWorkList] AS k
                                            ON  (tpae.ProcInstID = k.ProcInstID  AND (
                                               k.[WorkflowName] = 'KQF'
                                               OR k.[WorkflowName] = 'PIP'
                                               OR k.[WorkflowName] = 'LSF'
                                               OR k.[WorkflowName] = 'KYF'
                                               OR k.[WorkflowName] = 'KMF'
                                               OR k.[WorkflowName] = 'KBF'
                                               OR k.[WorkflowName] = 'PLF'
                                               OR k.[WorkflowName] = 'PUF'
                                       ) )
                                        left  JOIN T_Employee AS te ON (te.AccountName= Substring(k.[ActionerName],9,LEN(k.[ActionerName])-8) and  Substring(k.[ActionerName],9,LEN(k.[ActionerName])-8) !='')
                                WHERE   tpae.[State]=1
                                       AND tpae.UserID = @userCode
                                       AND tpae.PMStatus !='MarkFinished'
                                       AND tpbt.FlowStatus IN ('BPApproveMarked','FlowLaunch','UserMark','WaitUserMark')