引言，基礎：
TOP 字句允許指定 WITH TIES。（多行同位）
SELECT TOP 5 WITH TIES title_id,price,title_name
FROM title
ORDER BY price DESC
結果：
title_id price title_name
-------- --------------------- ------------------------------
Tt005 80.0000 Title_Name_5
Tt013 70.0000 Title_Name_13
Tt012 65.0000 Title_Name_12
Tt010 55.0000 Title_Name_10
Tt002 50.0000 Title_Name_2
Tt004 50.0000 Title_Name_4
Tt008 50.0000 Title_Name_8
Tt014 50.0000 Title_Name_14
(8 row(s) affected)
方法一：標准的 SQL 方法：利用視圖
對於大表來說，性能將顯著降低，因為對每一行都要掃描一次該表。
CREATE VIEW ranked_sales(rank,title_id,price,title_name)
AS
SELECT
(SELECT COUNT(DISTINCT T2.price) FROM title AS T2
WHERE T2.price>=T1.price) AS rank,
title_id,
price,
title_name
FROM title AS T1 WHERE price IS NOT NULL
GO
SELECT * FROM ranked_sales WHERE rank<=10 ORDER BY rank
GO
結果：
rank title_id price title_name
----------- -------- --------------------- ------------------------------
1 Tt005 80.0000 Title_Name_5
2 Tt013 70.0000 Title_Name_13
3 Tt012 65.0000 Title_Name_12
4 Tt010 55.0000 Title_Name_10
5 Tt002 50.0000 Title_Name_2
5 Tt004 50.0000 Title_Name_4
5 Tt008 50.0000 Title_Name_8
5 Tt014 50.0000 Title_Name_14
6 Tt001 40.0000 Title_Name_1
7 Tt006 39.0000 Title_Name_6
8 Tt007 38.0000 Title_Name_7
9 Tt009 35.0000 Title_Name_9
10 Tt011 33.0000 Title_Name_11
方法二：標准的 SQL 方法：不利用視圖
用導出表代替了視圖，性能與方法一相當，沒有改善。結果也一樣。
SELECT rank,title_id,price,title_name
FROM(SELECT
T1.title_id,
price,
T1.title_name,
(SELECT COUNT(DISTINCT T2.price) FROM title AS T2
WHERE T1.price<=T2.price) AS rank
FROM title AS T1) AS X
WHERE price IS NOT NULL
ORDER BY rank
方法三：利用臨時表（帶有“一致性”列而沒有多行同位）
速度較快。的不區分“多行同位”值。
CREATE TABLE #ranked_order
(
rank int IDENTITY NOT NULL PRIMARY KEY,
title_id char(6) NOT NULL,
price int NOT NULL,
title_name varchar(30) NOT NULL
)
GO
INSERT #ranked_order
SELECT title_id,price,title_name FROM title
WHERE price IS NOT NULL ORDER BY price DESC
SELECT * FROM #ranked_order
DROP TABLE #ranked_order
結果：
(16 row(s) affected)
rank title_id price title_name
----------- -------- ----------- ------------------------------
1 Tt005 80 Title_Name_5
2 Tt013 70 Title_Name_13
3 Tt012 65 Title_Name_12
4 Tt010 55 Title_Name_10
5 Tt002 50 Title_Name_2
6 Tt004 50 Title_Name_4
7 Tt008 50 Title_Name_8
8 Tt014 50 Title_Name_14
9 Tt001 40 Title_Name_1
10 Tt006 39 Title_Name_6
11 Tt007 38 Title_Name_7
12 Tt009 35 Title_Name_9
13 Tt011 33 Title_Name_11
14 Tt016 30 Title_Name_16
15 Tt015 25 Title_Name_15
16 Tt003 20 Title_Name_3
方法四：利用臨時表（帶有“一致性”列和“多行同位”）
CREATE TABLE #ranked_order
(
rank int IDENTITY NOT NULL,
title_id char(6) NOT NULL,
price int NOT NULL,
title_name varchar(30) NOT NULL
)
GO
INSERT #ranked_order
SELECT title_id,price,title_name FROM title
WHERE price IS NOT NULL ORDER BY price DESC
SELECT B.rank,A.title_id,B.price,A.title_name
FROM(
SELECT MIN(T2.rank) AS rank,T2.price FROM #ranked_order AS T2
GROUP BY T2.price) AS B,
#ranked_order AS A
WHERE A.price=B.price
ORDER BY B.rank
DROP TABLE #ranked_order
結果：
(16 row(s) affected)
rank title_id price title_name
----------- -------- ----------- ------------------------------
1 Tt005 80 Title_Name_5
2 Tt013 70 Title_Name_13
3 Tt012 65 Title_Name_12
4 Tt010 55 Title_Name_10
5 Tt002 50 Title_Name_2
5 Tt004 50 Title_Name_4
5 Tt008 50 Title_Name_8
5 Tt014 50 Title_Name_14
9 Tt001 40 Title_Name_1
10 Tt006 39 Title_Name_6
11 Tt007 38 Title_Name_7
12 Tt009 35 Title_Name_9
13 Tt011 33 Title_Name_11
14 Tt016 30 Title_Name_16
15 Tt015 25 Title_Name_15
16 Tt003 20 Title_Name_3
(16 row(s) affected)
指明有多少值處於“多行同位”狀態。
CREATE TABLE #ranked_order
(
rank int IDENTITY NOT NULL,
title_id char(6) NOT NULL,
price int NOT NULL,
title_name varchar(30) NOT NULL
)
GO
INSERT #ranked_order
SELECT title_id,price,title_name FROM title
WHERE price IS NOT NULL ORDER BY price DESC
SELECT B.rank,
CASE B.number_tIEd
WHEN 1 THEN ''''
ELSE ''(''+CONVERT(VARCHAR(10),number_tied)+'' Way TIE)''
END AS tIE,
A.title_id,
B.price,
A.title_name
FROM(
SELECT MIN(T2.rank) AS rank,COUNT(*) AS number_tIEd,T2.price
FROM #ranked_order AS T2
GROUP BY T2.price) AS B,#ranked_order AS A
WHERE A.price=B.price
ORDER BY B.rank
DROP TABLE #ranked_order
結果：
(16 row(s) affected)
rank tIE title_id price title_name
----------- -------------------- -------- ----------- ------------------------------
1 Tt005 80 Title_Name_5
2 Tt013 70 Title_Name_13
3 Tt012 65 Title_Name_12
4 Tt010 55 Title_Name_10
5 (4 Way TIE) Tt002 50 Title_Name_2
5 (4 Way TIE) Tt004 50 Title_Name_4
5 (4 Way TIE) Tt008 50 Title_Name_8
5 (4 Way TIE) Tt014 50 Title_Name_14
9 Tt001 40 Title_Name_1
10 Tt006 39 Title_Name_6
11 Tt007 38 Title_Name_7
12 Tt009 35 Title_Name_9
13 Tt011 33 Title_Name_11
14 Tt016 30 Title_Name_16
15 Tt015 25 Title_Name_15
16 Tt003 20 Title_Name_3
方法五：利用游標
SELECT語句（集合操作）可能需要相關子查詢或自聯結來解決，這樣就需要多次訪問數據。這類問題特別適合采用一種方法來解決，因為采用游標法時，只需掃描一次數據。對於一些時序操作，游標法比單個SELECT語句效率高。（但利用游標不如利用臨時表速度快）。
處理“多行同位”的三個原則：
1、 對“多行同位”指定重復的等級值，並且下一個不重復的等級值遞增1。
2、 各行（包括“多行同位”）的等級值都唯一。
3、 指定重復的等級值，但是下一個不重復的等級值由它在等級中的實際位置決定，而非簡單地遞增1。
方法五A：利用游標（對“多行同位”應用“規則1”）
DECLARE @rank int,@title_id char(6),@price int,
@title_name varchar(30),@last_rank int,
@last_prace money,@counter int
SELECT @rank=1,@last_rank=1,@last_prace=0,@counter=1
DECLARE rank_cursor CURSOR FOR
SELECT title_id,price,title_name
FROM title WHERE price IS NOT NULL
ORDER BY price DESC
OPEN rank_cursor
FETCH NEXT FROM rank_cursor INTO @title_id,@price,@title_name
WHILE (@@FETCH_STATUS<>-1)
BEGIN
IF (@counter=1) --For first row,just display values
--and set last values
BEGIN
SELECT rank=@rank,title_id=@title_id,
price=@price,title_name=@title_name
END
ELSE
BEGIN
--If current PRICE the same as last,assign the same rank
IF (@price=@last_prace)
SELECT rank=@last_rank,title_id=@title_id,
price=@price,title_name=@title_name
ELSE --Otherwise,increment the rank
BEGIN
SELECT rank=@last_rank+1,title_id=@title_id,
price=@price,title_name=@title_name
SELECT @rank=@last_rank+1
END
END
--Set values to current row
SELECT @counter=@counter+1,@last_rank=@rank,
@last_prace=@price
FETCH NEXT FROM rank_cursor INTO @title_id,@price,@title_name
END
CLOSE rank_cursor
DEALLOCATE rank_cursor
結果：
rank title_id price title_name
----------- -------- ----------- ------------------------------
1 Tt005 80 Title_Name_5
2 Tt013 70 Title_Name_13
3 Tt012 65 Title_Name_12
4 Tt010 55 Title_Name_10
5 Tt002 50 Title_Name_2
5 Tt004 50 Title_Name_4
5 Tt008 50 Title_Name_8
5 Tt014 50 Title_Name_14
6 Tt001 40 Title_Name_1
7 Tt006 39 Title_Name_6
8 Tt007 38 Title_Name_7
9 Tt009 35 Title_Name_9
10 Tt011 33 Title_Name_11
11 Tt016 30 Title_Name_16
12 Tt015 25 Title_Name_15
13 Tt003 20 Title_Name_3
方法五B：利用游標（對“多行同位”應用“規則2”）
DECLARE @rank int,@title_id char(6),@price int,
@title_name varchar(30)
SELECT @rank=1
DECLARE rank_cursor CURSOR FOR
SELECT title_id,price,title_name
FROM title WHERE price IS NOT NULL
ORDER BY price DESC
OPEN rank_cursor
FETCH NEXT FROM rank_cursor INTO @title_id,@price,@title_name
WHILE (@@FETCH_STATUS<>-1)
BEGIN
SELECT rank=@rank,title_id=@title_id,
price=@price,title_name=@title_name
SELECT @rank=@rank+1
FETCH NEXT FROM rank_cursor INTO @title_id,@price,@title_name
END
CLOSE rank_cursor
DEALLOCATE rank_cursor
結果：
rank title_id price title_name
----------- -------- ----------- ------------------------------
1 Tt005 80 Title_Name_5
2 Tt013 70 Title_Name_13
3 Tt012 65 Title_Name_12
4 Tt010 55 Title_Name_10
5 Tt002 50 Title_Name_2
6 Tt004 50 Title_Name_4
7 Tt008 50 Title_Name_8
8 Tt014 50 Title_Name_14
9 Tt001 40 Title_Name_1
10 Tt006 39 Title_Name_6
11 Tt007 38 Title_Name_7
12 Tt009 35 Title_Name_9
13 Tt011 33 Title_Name_11
14 Tt016 30 Title_Name_16
15 Tt015 25 Title_Name_15
16 Tt003 20 Title_Name_3
方法五C：利用游標（對“多行同位”應用“規則3”）
DECLARE @rank int,@title_id char(6),@price int,
@title_name varchar(30),@last_rank int,
@last_prace money,@counter int
SELECT @rank=1,@last_rank=1,@last_prace=0,@counter=1
DECLARE rank_cursor CURSOR FOR
SELECT title_id,price,title_name
FROM title WHERE price IS NOT NULL
ORDER BY price DESC
OPEN rank_cursor
FETCH NEXT FROM rank_cursor INTO @title_id,@price,@title_name
WHILE (@@FETCH_STATUS<>-1)
BEGIN
IF (@counter=1) --For first row,just display values
--and set last values
BEGIN
SELECT rank=@rank,title_id=@title_id,
price=@price,title_name=@title_name
END
ELSE
BEGIN
--If current PRICE the same as last,assign the same rank
IF (@price=@last_prace)
SELECT rank=@last_rank,title_id=@title_id,
price=@price,title_name=@title_name
ELSE --Otherwise,set the rank to the overrall
--counter of how many rows have been visited
BEGIN
SELECT @rank=@counter
SELECT rank=@rank,title_id=@title_id,
price=@price,title_name=@title_name
END
END
--Set values to current row
SELECT @counter=@counter+1,@last_rank=@rank,
@last_prace=@price
FETCH NEXT FROM rank_cursor INTO @title_id,@price,@title_name
END
CLOSE rank_cursor
DEALLOCATE rank_cursor
結果：
rank title_id price title_name
----------- -------- ----------- ------------------------------
1 Tt005 80 Title_Name_5
2 Tt013 70 Title_Name_13
3 Tt012 65 Title_Name_12
4 Tt010 55 Title_Name_10
5 Tt002 50 Title_Name_2
5 Tt004 50 Title_Name_4
5 Tt008 50 Title_Name_8
5 Tt014 50 Title_Name_14
9 Tt001 40 Title_Name_1
10 Tt006 39 Title_Name_6
11 Tt007 38 Title_Name_7
12 Tt009 35 Title_Name_9
13 Tt011 33 Title_Name_11
14 Tt016 30 Title_Name_16
15 Tt015 25 Title_Name_15
16 Tt003 20 Title_Name_3
