再看case when 在row_number中的使用

来源：互联网发布：怎么选购加湿器知乎编辑：程序博客网时间：2024/06/06 07:37
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')
0 0