茫茫網海中的冷日 - [轉貼]SQL Server 2005 - 如何讓 SELECT 查詢結果額外增加自動遞增序號

DB研討會 : [轉貼]SQL Server 2005 - 如何讓 SELECT 查詢結果額外增加自動遞增序號

發表者

討論內容

冷日
(冷日)

發表時間：2014/5/3 6:58

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]SQL Server 2005 - 如何讓 SELECT 查詢結果額外增加自動遞增序號: 2006 12 08 12 45 SQL Server 2005 - 如何讓 SELECT 查詢結果額外增加自動遞增序號

圖表1
如果資料表本身並不內含自動地增編號的欄位時，要怎麼作，才能夠讓 SELECT 查詢結果如圖表 1 所示地額外增加自動遞增序號呢？我們提供下列五種方法供您參考：
USE 北風貿易 ;

GO

/* 方法一 */

SELECT 序號 = ( SELECT COUNT ( 客戶編號 ) FROM 客戶 AS LiMing
WHERE LiMing . 客戶編號 <= Chang . 客戶編號 ),
客戶編號 , 公司名稱
FROM 客戶 AS
Chang ORDER BY 1 ;
GO

/* 方法二 : 使用 SQL Server 2005 獨有的 RANK() OVER () 語法 */
SELECT RANK() OVER (ORDER BY 客戶編號 DESC) AS 序號 ,
客戶編號 , 公司名稱
FROM 客戶 ;
GO

/* 方法三 */
SELECT 序號
= COUNT (*), LiMing . 客戶編號 , LiMing . 公司名稱
FROM 客戶 AS LiMing , 客戶 AS Chang
WHERE LiMing . 客戶編號 >= Chang . 客戶編號
GROUP BY LiMing . 客戶編號 , LiMing . 公司名稱
ORDER BY 序號
;
GO

/* 方法四
建立一個「自動編號」的欄位，然後將資料新增至一個區域性暫存資料表，
然後由該區域性暫存資料表中，將資料選取出來，最後刪除該區域性暫存資料表
*/
SELECT 序號 = IDENTITY(INT,1,1) , 管道 , 程式語言 , 講師 , 資歷
INTO #LiMing
FROM 問券調查一 ;
GO
SELECT * FROM #LiMing ;
GO

DROP TABLE #LiMing ;
GO

/*
方法五
使用 SQL Server 2005 獨有的 ROW_NUMBER() OVER () 語法
搭配 CTE （一般資料表運算式，就是 WITH 那段語法）選取序號 2 ~ 4 的資料
*/
WITH 排序後的圖書 AS
( SELECT ROW_NUMBER () OVER ( ORDER BY 客戶編號
DESC ) AS 序號 ,
客戶編號 , 公司名稱
FROM 客戶 )
SELECT * FROM 排序後的圖書
WHERE 序號 BETWEEN 2 AND 4 ;
GO
原文出處：SQL Server 2005 - 如何讓 SELECT 查詢結果額外增加自動遞增序號 @ 章立民研究室 :: 隨意窩 Xuite日誌

回覆

冷日
(冷日)

發表時間：2014/5/3 7:00

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]為查詢的結果加上序號

[SQL]為查詢的結果加上序號(ROW_NUMBER,RANK,OVER)

在MS SQL2005以後，增加了一些幫查詢結果加上序號的函數

以下的範例使用北風(NorthWind)資料庫

介紹如下：

1.ROW_NUMBER

依照指定的欄位排序，並逐筆加上順號的方式

例如：


SELECT
	ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID
	,*
FROM Orders

2.RANK

依照排序的欄位，相同的資料相同排名，下一個不同會【跳脫】


SELECT
	RANK() OVER(ORDER BY CustomerID) AS ROWID
	,*
FROM Orders

3.DENSE_RANK

依照排序的欄位，相同的資料相同排名，下一個不同會【不跳脫】


SELECT
	--ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID
	--RANK() OVER(ORDER BY CustomerID) AS ROWID
	DENSE_RANK() OVER(ORDER BY CustomerID) AS ROWID
	,*
FROM Orders

小喵筆記一下以免忘記

原文出處： [SQL]為查詢的結果加上序號(ROW_NUMBER,RANK,OVER) - topcat 姍舞之間的極度凝聚- 點部落

回覆

冷日
(冷日)

發表時間：2014/5/3 7:03

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]如何用 SELECT 查詢產生一個自動遞增的序號欄位

[轉][MySQL] 如何用 SELECT 查詢產生一個自動遞增的序號欄位

週一, 20 一月 2014 18:53 管理者

本文轉自： i丹尼

因為要跑活動績分排行，而這個排行又是以某項欄位總和做排序給績分，所以希望撈出的資料在 SUM, GROUP 及 ORDER 後，還可以再依序給一個排行績分的欄位。

因為是持續三週的活動，且每天都有每日的績分排行(前20名的績分依序就是20,19,18...到1分)，每週還有週排行，週排行就是將每個Player該週的績分加總排序即可。

所以用了一個活動資料表，來記錄每個玩家每日的績分:

CREATE TABLE `tbl_event_A` (
`sn` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'SN',
`playerid` int(11) unsigned NOT NULL COMMENT 'PlayerID',
`logday` int(11) unsigned NOT NULL COMMENT '記錄日期',

`winpoint` int(11) NOT NULL COMMENT '日總贏點數',
`score` int(11) NOT NULL COMMENT '日績分',
`week` tinyint(3) NOT NULL COMMENT '週次',
PRIMARY KEY (`sn`),
KEY `logdate` (`logday`)
) ENGINE=MyISAM;

希望從原始資料表撈出的排序資料及績分，可以在一個 Query 動作中完成，所以會需要在 Select 動作中，帶出一個有遞增績分欄位。

上網做相關的搜尋，一開始連要搜尋怎麼的關鍵詞都摸不著頭序，就一直試，後來發現搜尋 " select 查詢自動序號" 這樣的結果最多相關的結果。
有不少回應是 MS SQL 本身才支援的語法，像是 RANK() OVER() ROW_NUMBER() 語法，可參考這裡

反而 MySQL 的討論不多，後來後搜尋英文
mysql select auto increment number ，才有較多的
討論。

原來是利用 MySQL 中臨時(暫時)變數的方式來做遞增，簡單範例如下：

SELECT @s:=@s+1 serial_number, student_id, student_name, student_avg
FROM students, (SELECT @s:= 0) AS s
WHERE student_avg > 4;

於是馬上就把它套用到我的查詢裡面：

SELECT @SN:=@SN+1 as Score, PlayerID, SUM(winpoint) as WinPoint FROM tbl_dailyplayerpoint, (SELECT @SN:=0) as Dummy WHERE logday='$Yesterday' GROUP BY PlayerID ORDER BY WinPoint DESC LIMIT 20;

試了發現的確可以產生一個遞增的欄位，但放在我的 query 中，經過 SUM, GROUP 及 ORDER BY 後，帶出的數值很奇怪，是一些很大的數字，且沒有規律。

+-------+----------+----------+
| Score | PlayerID | WinPoint |
+-------+----------+----------+
|    31 |        4 | 20441389 |
|    60 |       31 | 12158264 |
|    35 |       10 | 11551464 |
|    41 |       23 |  8594067 |
|    46 |       34 |  7555544 |
|    29 |        1 |  7475172 |
|    42 |       24 |  6409297 |
|    62 |       11 |  6393142 |
|    78 |       54 |  5791644 |
|    45 |       33 |  5757180 |
|    47 |       35 |  5689378 |
|    70 |       44 |  5649346 |
|    34 |        8 |  5590604 |
|    86 |       56 |  5470747 |
|    44 |       32 |  5289877 |
|    40 |       22 |  5080401 |
|    81 |       22 |  4591810 |
|     2 |       25 |  4446734 |
|    75 |       93 |  4431110 |
|    63 |       68 |  4430356 |
+-------+----------+----------+

於是在想，是不是因為這個臨時的變數在經過 GROUP 後，因為不同的資料量而被加總了不同次而不同，不是想像中的由小到大。

最後，經過不同的常試修改，試出了這個方式，也就是把 @SN:=@SN+1 移出原始query，把原查詢變成一個子查詢，@SN:=@SN+1放到外層，這樣 @SN 就不會受到 GROUP 的影響而產生不定數的值。

SELECT 21-(@SN:=@SN+1) as Score, PlayerID, WinPoint FROM (SELECT PlayerID, SUM(winpoint) as WinPoint FROM tbl_dailyplayerpoint WHERE logday='$Yesterday' GROUP BY PlayerID ORDER BY WinPoint DESC LIMIT 20) as Dummy1, (SELECT @SN:=0) as Dummy2;

撈出的結果看來就是我想要的!!

+-------+----------+----------+
| Score | PlayerID | WinPoint |
+-------+----------+----------+
|    20 |        4 | 20441389 |
|    19 |       31 | 12158264 |
|    18 |       10 | 11551464 |
|    17 |       23 |  8594067 |
|    16 |       34 |  7555544 |
|    15 |        1 |  7475172 |
|    14 |       24 |  6409297 |
|    13 |       11 |  6393142 |
|    12 |       54 |  5791644 |
|    11 |       33 |  5757180 |
|    10 |       35 |  5689378 |
|     9 |       44 |  5649346 |
|     8 |        8 |  5590604 |
|     7 |       56 |  5470747 |
|     6 |       32 |  5289877 |
|     5 |       22 |  5080401 |
|     4 |       22 |  4591810 |
|     3 |       25 |  4446734 |
|     2 |       93 |  4431110 |
|     1 |       68 |  4430356 |
+-------+----------+----------+

於是我就可以在一個 query 中，直接撈出並新增到所建立的活動資料表中了!!

INSERT INTO tbl_event_A (score,playerid,winpoint,logday,week) SELECT 21-(@SN:=@SN+1), PlayerID, WinPoint,'$Yesterday','$week' FROM (SELECT PlayerID, SUM(winpoint) as WinPoint FROM tbl_dailyplayerpoint WHERE logday='$Yesterday' GROUP BY PlayerID ORDER BY WinPoint DESC LIMIT 20) as Dummy1, (SELECT @SN:=0) as Dummy2;

寫好的 PHP 用 cron table 設定每日早上跑，去產生昨天的排行!!
以上經驗，分享給大家!!

原文出處：[轉][MySQL] 如何用 SELECT 查詢產生一個自動遞增的序號欄位

回覆

冷日
(冷日)

發表時間：2014/5/3 7:05

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]MySQL 如何讓 SELECT 查詢結果額外增加自動遞增序

MySQL 如何讓 SELECT 查詢結果額外增加自動遞增序

在 MySQL 5.0 環境運作，舉例，我有一個資料表如下：

table A

dd name
---- ---------
77 , aaaaa
99 , aaaa1
88 , aaaa2

有沒有辦法，在一段 Select 指令敘述，便能完成以下結果？

No dd name
---------------------
001 , 77 , aaaaa
002 , 99 , aaaa1
003 , 88 , aaaa2

謝謝。

最佳解答

SET @i =0;
SELECT * , (
@i := @i +1
) AS t1
FROM `table A`

MySQL 4.1以上
可以設變數
在最上面使用set然後在每一行自動加1
原則上這樣就可以了

PS.
以上SQL語法只有做自動加1並不做排序
如果要搭配LIMIT做分頁時
請用PHP來改變@i的初始值即可

2007-08-16 10:46:40 補充

如果要補0的話

請利用mysql的str_pad的函數

原文出處：MySQL 如何讓 SELECT 查詢結果額外增加自動遞增序 - Yahoo!奇摩知識+

回覆

DB研討會 : [轉貼]SQL Server 2005 - 如何讓 SELECT 查詢結果額外增加自動遞增序號

[SQL]為查詢的結果加上序號(ROW_NUMBER,RANK,OVER)

[轉][MySQL] 如何用 SELECT 查詢 產生一個自動遞增的序號欄位

[轉][MySQL] 如何用 SELECT 查詢產生一個自動遞增的序號欄位