欧美一级内谢_91中文字幕在线_羞羞视频官网_亚洲日韩中文字幕天堂不卡_在线观看日韩一区_国产成人激情

北京北大青鳥指導(dǎo):SQL2005中新增函數(shù)的用法


SQL server 2005新增的幾個函數(shù),分別是row_number( )、rank( )、,DENSE_RANK( )、ntile( )下面,北京北大青鳥通州校區(qū)ACCP學(xué)術(shù)部老師就以實例分別簡單講解。

1.row_number()
先來點數(shù)據(jù),先建個表

SET NOCOUNT ON
CREATE TABLE Person(
FirstName VARCHAR(10),
Age INT,
Gender CHAR(1))
INSERT INTO Person VALUES ('Ted',23,'M')
INSERT INTO Person VALUES ('John',40,'M')
INSERT INTO Person VALUES ('George',6,'M')
INSERT INTO Person VALUES ('Mary',11,'F')
INSERT INTO Person VALUES ('Sam',17,'M')
INSERT INTO Person VALUES ('Doris',6,'F')
INSERT INTO Person VALUES ('Frank',38,'M')
INSERT INTO Person VALUES ('Larry',5,'M')
INSERT INTO Person VALUES ('Sue',29,'F')
INSERT INTO Person VALUES ('Sherry',11,'F')
INSERT INTO Person VALUES ('Marty',23,'F')直接用例子說明問題:SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],
FirstName,
Age
FROM Person
出現(xiàn)的數(shù)據(jù)如下
Row Number by Age                FirstName            Age
--------------------------                 ----------            --------
1                                                Larry                   5
2                                                Doris                   6
3                                                George               6
4                                                Mary                   11
5                                                Sherry                 11
6                                                Sam                    17
7                                                Ted                     23
8                                                Marty                   23
9                                                Sue                     29
10                                              Frank                  38
11                                              John                    40可以觀察到,是根據(jù)年齡升序排列了,并且row_number()是給出了序列號了,這個序列號被重命名為Row Number by Age,與sql server2000對比:
如果在sql server2000中實現(xiàn)相對麻煩一些,我們可以利用IDENTITY()函數(shù)實現(xiàn),但IDENTITY()函數(shù)只能用在sql server2000臨時表中,因此需要將數(shù)據(jù)檢索到臨時表里。
select identity(int,1,1) as [Row Number by Age],FirstName,Age into #A from Person order by Age
select * from #A
drop table #a如果不想按年齡排序,可以這樣寫
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set], FirstName,Age FROM Person另外一個例子
SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], FirstName,Age,GenderFROM Person這里是按性別劃分區(qū)間了,同一性別再按年齡來排序,輸出結(jié)果如下
Partition by Gender        FirstName        Age               Gender -------------------- ---------- ----------- ------ 1                          Doris            6                 F 2                          Mary             11                F 3                          Sherry           11                F 4                          Sue              29                F 1                          Larry            5                 M 2                          George           6                 M 3                          Sam              17                M 4                          Ted              23                M 5                          Marty            23                M 6                          Frank            38                M 7                          John             40                M注意,姓名M開始,序號又從1,2,3開始了

2.RANK( )函數(shù)
先看例子
SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age], FirstName,Age FROM Person輸出如下:
Rank by Age                FirstName        Age -------------------- ---------- ----------- 1                          Larry            5 2                          Doris            6 2                          George           6 4                          Mary             11 4                          Sherry           11 6                          Sam              17 7                          Ted              23 7                          Marty            23 9                          Sue              29 10                         Frank            38 11                         John             40看到了么,同年嶺的話,將有相同的順序,順序成1,2,2,4了。與sql server2000對比:
出現(xiàn)了RANK()函數(shù)實在是方便,在sql server2000里實現(xiàn)排序并列的問題麻煩很多。
select [Rank by Age]=isnull((select count(*) from person where Age>A.Age),0)+1,FirstName,Age from Person A order by [Rank by Age]SELECT RANK() OVER(PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName, Age, Gender FROM Person輸出為Partition by Gender        FirstName        Age               Gender-------------------- ---------- ----------- ------ 1                          Doris            6                 F 2                          Mary             11                F 2                          Sherry           11                F 4                          Sue              29                F 1                          Larry            5                 M 2                          George           6                 M 3                          Sam              17                M 4                          Ted              23                M 4                          Marty            23                M 6                          Frank            38                M 7                          John             40                M
可以看到,按性別分組了,每個性別分組里,繼續(xù)是用了rank( )函數(shù)

3.DENSE_RANK( )函數(shù)
         SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age],
         FirstName,
         Age
         FROM Person

輸出結(jié)果為:
Dense Rank by Age          FirstName        Age
-------------------- ---------- -----------
1                          Larry            5
2                          Doris            6
2                          George           6
3                          Mary             11
3                          Sherry           11
4                          Sam              17
5                          Ted              23
5                          Marty            23
6                          Sue              29
7                          Frank            38
8                          John             40

看到了么,和rank函數(shù)區(qū)別是,順序始終是連續(xù)的,Doris 和George同年,都是排第2位,但之后的mary不象rank函數(shù)那樣排第4,而是排第3位了


4.ntile( )函數(shù)
SELECT FirstName,
Age,
NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
FROM Person

輸出結(jié)果:
FirstName        Age               Age Groups
---------- ----------- --------------------
Larry                5                  1
Doris                6                  1
George            6                  1
Mary                11                1
Sherry             11                 2
Sam                17                 2
Ted                 23                 2
Marty              23                 2
Sue                29                 3
Frank             38                 3
John               40                 3
這個函數(shù)按照ntile(n)中的N,把記錄強制分成多少段,11條記錄現(xiàn)在分成3段了,lary到mary是第1段,sherry到maty是第2段,sue到j(luò)ohn是第3段了。

北大青鳥網(wǎng)上報名
北大青鳥招生簡章
主站蜘蛛池模板: 国产精品精品久久久 | 国产精品一区二区三区四区 | 金莲网 | 免费一二区 | 日韩av中文 | 国产 日韩 欧美 在线 | 欧美精品一区二区三区在线播放 | 国产福利小视频 | 欧美精品一区二区三区在线播放 | 精品一区二区电影 | 成人欧美一区二区三区色青冈 | 日本精品久久久一区二区三区 | 国产精品欧美日韩 | 精品少妇一区二区三区在线播放 | 日韩欧美综合在线视频 | 狠狠躁躁夜夜躁波多野结依 | 国产福利视频 | 色黄爽 | 精品欧美乱码久久久久久 | 精精国产xxxx视频在线播放 | 97久久超碰| 亚洲精品毛片av | 欧美视频三区 | 欧美精品在线免费观看 | 久久99精品久久久久 | 日韩国产欧美一区 | 成人欧美一区二区三区视频xxx | 成人毛片视频免费 | 国产在线a | 国产日韩电影 | 亚洲bt 欧美bt 日本bt | 五月激情综合 | 久综合| 色噜噜色综合 | 欧美日韩高清在线一区 | 国产精品性做久久久久久 | 久久精品视频91 | 日韩一区二区在线播放 | 一区二区福利视频 | 国产精品久久性 | 97国产在线视频 |