[SQL]SQL Script分頁時，要如何除重覆的資料?

2014-03-12

1861
0
SQL

透過 ROW_NUMBER 來分頁時，要如何除重覆的資料呢?

最近跟同事Cooper討論MS SQL透過 ROW_NUMBER 來分頁(Paging)時，要如何除重覆的資料呢?

用資料來測試比較清楚，如下，

USE tempdb
go

IF OBJECT_ID('EmpInfo') IS NOT NULL 
	DROP TABLE EmpInfo;


CREATE TABLE EmpInfo(
CNAME NVARCHAR(30)
, DEP_NAME NVARCHAR(30)
);
go

INSERT INTO EmpInfo(CNAME, DEP_NAME) VALUES(N'亂馬客', N'技術辦公室');
INSERT INTO EmpInfo(CNAME, DEP_NAME) VALUES(N'大頭狗', N'狗狗辦公室');
INSERT INTO EmpInfo(CNAME, DEP_NAME) VALUES(N'沈小燕', N'人力資源部');
INSERT INTO EmpInfo(CNAME, DEP_NAME) VALUES(N'大頭狗', N'狗狗辦公室');
INSERT INTO EmpInfo(CNAME, DEP_NAME) VALUES(N'沈小燕', N'人力資源部');

SELECT * FROM EmpInfo ORDER BY CNAME DESC;

如果直接取 ROW_NUMBER 出來再 DISTINCT ，那原本重覆的資料就不會被過濾掉!

因為 ROW_NUMBER 產生出來的值是流水號，所以一定不會重覆，如下，

WITH ctePaging 
AS (
	SELECT ROW_NUMBER() OVER (ORDER BY CNAME DESC) AS sn ,*
	FROM EmpInfo 
) 
SELECT DISTINCT * FROM ctePaging;

那要如何做呢? 就是先 DISTINCT 後，再加入 ROW_NUMBER 去產出流水號，如下，

WITH ctePaging 
AS (
	SELECT ROW_NUMBER() OVER (ORDER BY CNAME DESC) AS sn, *
	FROM (
		SELECT DISTINCT *
		FROM EmpInfo 
	)S
) 
SELECT * FROM ctePaging;

另外，同事Cooper有另一個方法，就是用 DENSE_RANK 去讓重覆的資料，排名是相同的，然後再去 DISTINCT，這樣也是可以的，如下，

WITH ctePaging 
AS (
	SELECT  DENSE_RANK() OVER (ORDER BY CNAME DESC) AS sn, *
	FROM EmpInfo 
)
SELECT DISTINCT * FROM ctePaging;

那再來就是說，如果SQL中沒有 SELECT 欄位的話，資料的排序就不太對，如下2個SQL，第2個SQL只輸出 CNAME 及 DEP_NAME ，而它輸出的資料順序卻和有輸出流水號(sn) 不相同，

WITH ctePaging 
AS (
	SELECT ROW_NUMBER() OVER (ORDER BY CNAME DESC) AS sn, *
	FROM (
		SELECT DISTINCT *
		FROM EmpInfo 
	)S
) 
SELECT * FROM ctePaging;

WITH ctePaging 
AS (
	SELECT ROW_NUMBER() OVER (ORDER BY CNAME DESC) AS sn, *
	FROM (
		SELECT DISTINCT *
		FROM (
			SELECT * FROM EmpInfo 
		) T
	)S
) 
SELECT CNAME,DEP_NAME  FROM ctePaging;