去重 ROW_NUMBER() OVER(PARTITION BY 分組欄位 ORDER BY 排序欄位) RN

標籤：技術分享   order   stat   .com   row   back   欄位   over   uid   

關鍵字  ROW_NUMBER() OVER(PARTITION BY 分組欄位 ORDER BY 排序欄位) RN 

   按照分組欄位進行排序並標編號

ROW_NUMBER() OVER(PARTITION BY 分組欄位 ORDER BY 排序欄位) RN

文法

ROW_NUMBER() OVER(PARTITION BY XMJL_UID ORDER BY XMCREATETIME) RN

 

執行個體

SELECT XMJL_UID,XMJL,XMCREATETIME,ROW_NUMBER() OVER(PARTITION BY XMJL_UID ORDER BY XMCREATETIME) RN FROM SHZT_XMGL WHERE XMSTATUS=‘進行中‘ ORDER BY XMCREATETIME

 

SELECT * FROM (SELECT  XMJL_UID,XMJL,XMCREATETIME,ROW_NUMBER() OVER(PARTITION BY XMJL_UID ORDER BY XMCREATETIME) RN   FROM G_INFOS WHERE  B.XMSTATUS=‘進行中‘ ORDER BY XMCREATETIME) WHERE RN=1

 

 

去重 ROW_NUMBER() OVER(PARTITION BY 分組欄位 ORDER BY 排序欄位) RN