sqlserver 用 RowNumber 分組

標籤：style   blog   color   io   os   ar   for   sp   div   

 1 SELECT RECORD.[RECORD_ID] 2       ,RECORD.[WORKFLOW_INFO_ID] 3       ,RECORD.[FORM_CODE] 4       ,RECORD.[APPLY_DATE] 5       ,RECORD.[APPLY_EMP_ID] 6       ,RECORD.[APPLY_EMP_NUMBER] 7       ,RECORD.[APPLY_EMP_NAME_EN] 8       ,RECORD.[APPLY_EMP_NAME_CN] 9       ,RECORD.[APPLY_EMP_EMAIL]10       ,RECORD.[APPLY_EMP_PHONE]11       ,RECORD.[APPLY_EMP_MOBILE]12       ,RECORD.[APPLY_EMP_POSITION_CN]13       ,RECORD.[APPLY_EMP_POSITION_EN]14       ,RECORD.[APPLY_EMP_WORK_OFFICE]15       ,RECORD.[APPLY_EMP_WORK_AREA_CODE]16       ,RECORD.[APPLY_EMP_DEPT_CODE]17       ,RECORD.[APPLY_EMP_DEPT_TYPE]18       ,RECORD.[APPLY_EMP_DEPT_IS_FUNCTIONAL]19       ,RECORD.[APPLY_EMP_DEPT_NAME_CN]20       ,RECORD.[APPLY_EMP_DEPT_NAME_EN]21       ,RECORD.[APPLY_EMP_DEPT_FUNC_CODE]22       ,RECORD.[APPLY_EMP_DEPT_FUNC_CN]23       ,RECORD.[APPLY_EMP_DEPT_FUNC_EN]24       ,RECORD.[FILLER_EMP_ID]25       ,RECORD.[FILLER_EMP_NUMBER]26       ,RECORD.[FILLER_EMP_NAME_EN]27       ,RECORD.[FILLER_EMP_NAME_CN]28       ,RECORD.[FILLER_EMP_EMAIL]29       ,RECORD.[FILLER_EMP_PHONE]30       ,RECORD.[FILLER_EMP_MOBILE]31       ,RECORD.[FILLER_EMP_POSITION_CN]32       ,RECORD.[FILLER_EMP_POSITION_EN]33       ,RECORD.[FILLER_EMP_WORK_OFFICE]34       ,RECORD.[FILLER_EMP_WORK_AREA_CODE]35       ,RECORD.[FILLER_EMP_DEPT_CODE]36       ,RECORD.[FILLER_EMP_DEPT_TYPE]37       ,RECORD.[FILLER_EMP_DEPT_IS_FUNCTIONAL]38       ,RECORD.[FILLER_EMP_DEPT_NAME_CN]39       ,RECORD.[FILLER_EMP_DEPT_NAME_EN]40       ,RECORD.[FILLER_EMP_DEPT_FUNC_CN]41       ,RECORD.[FILLER_EMP_DEPT_FUNC_EN]42       ,RECORD.[FILLER_EMP_DEPT_FUNC_CODE]43       ,EMP.APPROVE_EMP_ID44       ,EMP.APPROVE_TYPE45       ,EMP.EMP_NAME_CN46       ,EMP.EMP_NAME_EN47       ,EMP.EMP_NUMBER48       ,EMP.SOURCE_EMP_NAME_CN49       ,EMP.SOURCE_EMP_NAME_EN50       ,EMP.SOURCE_EMP_NUMBER      51       ,Activity.ACTIVITY_NAME --活動名稱52       ,Activity.MEETING_CATEGORY_NAME53       ,Activity.MEETING_CATEGORY_CODE54       ,Activity.ACTIVITY_JOIN_NUMBER55       ,Activity.START_TIME56       ,Activity.FINISH_TIME57       ,Activity.FEE58       ,Activity.[ACTIVITY_REGION_NAME] 59       ,CASE WHEN(EMP.WF_ACTIVITY_NAME IS NULL) THEN 60         (CASE 61          WHEN(RECORD.RECORD_STATUS=0) THEN ‘Draft‘62          WHEN(RECORD.RECORD_STATUS=2) THEN ‘Completed‘63          WHEN (RECORD.RECORD_STATUS=4) THEN ‘Circulation‘64          WHEN (RECORD.RECORD_STATUS=5) THEN ‘Cancelled‘65          WHEN (RECORD.RECORD_STATUS=7) THEN ‘Suspend‘ ELSE ‘Unknown‘ END)66         ELSE EMP.WF_ACTIVITY_NAME END AS CURRENT_ACTIVITY_NAME67   FROM [dbo].[TSTB_COM_MS_ITEM_RECORD] RECORD 68   LEFT JOIN [dbo].FUN_MS_GetActivityInfo()  Activity69   ON RECORD.RECORD_ID = Activity.RECORD_ID70   LEFT JOIN71       (SELECT  * , ROW_NUMBER() OVER (PARTITION BY RECORD_ID ORDER BY CREATE_DATE )   AS RN  FROM  [dbo].[TSTB_COM_ITEM_RECORD_APPROVE_EMP] WHERE STATUS=1 AND APPROVE_TYPE<>2 AND WORKFLOW_PREFIX=‘MS‘) 72       AS EMP   ON RECORD.RECORD_ID=EMP.RECORD_ID AND EMP.RN=173   WHERE RECORD.STATUS=1 and RECORD.RECORD_STATUS<>5 

 

sqlserver 用 RowNumber 分組