SQL新函数, 排名函数

ROW_NUMBER()

根据over后的order by字据的字段排序,返回一个不断递增的整数.

use Northwind
go

select CompanyName, ContactName, City,
        ROW_NUMBER() over (order by City) as RowNum
from Customers
order by City, CompanyName

 

结果:

 

CompanyName                              ContactName                    City            RowNum
---------------------------------------- ------------------------------ --------------- --------------------
Drachenblut Delikatessen                 Sven Ottlieb                   Aachen          1
Rattlesnake Canyon Grocery               Paula Wilson                   Albuquerque     2
Old World Delicatessen                   Rene Phillips                  Anchorage       3
Vaffeljernet                             Palle Ibsen                    Århus           4
Galería del gastrónomo                   Eduardo Saavedra               Barcelona       5
LILA-Supermercado                        Carlos González                Barquisimeto    6
Magazzini Alimentari Riuniti             Giovanni Rovelli               Bergamo         7
Alfreds Futterkiste                      Maria Anders                   Berlin          8
Chop-suey Chinese                        Yang Wang                      Bern            9
Save-a-lot Markets                       Jose Pavarotti                 Boise           10
Folk och fä HB                           Maria Larsson                  Bräcke          11
Königlich Essen                          Philip Cramer                  Brandenburg     12
Maison Dewey                             Catherine Dewey                Bruxelles       13
Cactus Comidas para llevar               Patricio Simpson               Buenos Aires    16
Océano Atlántico Ltda.                   Yvonne Moncada                 Buenos Aires    14
Rancho grande                            Sergio Gutiérrez               Buenos Aires    15
The Cracker Box                          Liu Wong                       Butte           17
Gourmet Lanchonetes                      André Fonseca                  Campinas        18
GROSELLA-Restaurante                     Manuel Pereira                 Caracas         19
Suprêmes délices                         Pascale Cartrain               Charleroi       20
Hungry Owl All-Night Grocers             Patricia McKenna               Cork            21
Island Trading                           Helen Bennett                  Cowes           22
QUICK-Stop                               Horst Kloss                    Cunewalde       23
Hungry Coyote Import Store               Yoshi Latimer                  Elgin           24
Great Lakes Food Market                  Howard Snyder                  Eugene          25
Lehmanns Marktstand                      Renate Messner                 Frankfurt a.M.  26
Richter Supermarkt                       Michael Holz                   Genève          27
Ernst Handel                             Roland Mendel                  Graz            28
Wilman Kala                              Matti Karttunen                Helsinki        29
LINO-Delicateses                         Felipe Izquierdo               I. de Margarita 30
Trail's Head Gourmet Provisioners        Helvetius Nagy                 Kirkland        31
Simons bistro                            Jytte Petersen                 Kobenhavn       32
Ottilies Käseladen                       Henriette Pfalzheim            Köln            33
Split Rail Beer & Ale                    Art Braunschweiger             Lander          34
Morgenstern Gesundkost                   Alexander Feuer                Leipzig         35
Folies gourmandes                        Martine Rancé                  Lille           36
Furia Bacalhau e Frutos do Mar           Lino Rodriguez                 Lisboa          37
Princesa Isabel Vinhos                   Isabel de Castro               Lisboa          38
Around the Horn                          Thomas Hardy                   London          44
B's Beverages                            Victoria Ashworth              London          42
Consolidated Holdings                    Elizabeth Brown                London          43
Eastern Connection                       Ann Devon                      London          41
North/South                              Simon Crowther                 London          40
Seven Seas Imports                       Hari Kumar                     London          39
Berglunds snabbköp                       Christina Berglund             Luleå           45
Victuailles en stock                     Mary Saveley                   Lyon            46
Bólido Comidas preparadas                Martín Sommer                  Madrid          47
FISSA Fabrica Inter. Salchichas S.A.     Diego Roel                     Madrid          48
Romero y tomillo                         Alejandra Camino               Madrid          49
Blauer See Delikatessen                  Hanna Moos                     Mannheim        50
Bon app'                                 Laurence Lebihan               Marseille       51
Ana Trujillo Emparedados y helados       Ana Trujillo                   México D.F.     52
Antonio Moreno Taquería                  Antonio Moreno                 México D.F.     53
Centro comercial Moctezuma               Francisco Chang                México D.F.     54
Pericles Comidas clásicas                Guillermo Fernández            México D.F.     55
Tortuga Restaurante                      Miguel Angel Paolino           México D.F.     56
Mère Paillarde                           Jean Fresnière                 Montréal        57
Frankenversand                           Peter Franken                  München         58
Toms Spezialitäten                       Karin Josephs                  Münster         59
Du monde entier                          Janine Labrune                 Nantes          61
France restauration                      Carine Schmitt                 Nantes          60
Wartian Herkku                           Pirkko Koskitalo               Oulu            62
Paris spécialités                        Marie Bertrand                 Paris           63
Spécialités du monde                     Dominique Perrier              Paris           64
Lonesome Pine Restaurant                 Fran Wilson                    Portland        65
The Big Cheese                           Liz Nixon                      Portland        66
Reggiani Caseifici                       Maurizio Moroni                Reggio Emilia   67
Vins et alcools Chevalier                Paul Henriot                   Reims           68
Wellington Importadora                   Paula Parente                  Resende         69
Hanari Carnes                            Mario Pontes                   Rio de Janeiro  72
Que Delícia                              Bernardo Batista               Rio de Janeiro  71
Ricardo Adocicados                       Janete Limeira                 Rio de Janeiro  70
Piccolo und mehr                         Georg Pipps                    Salzburg        73
HILARION-Abastos                         Carlos Hernández               San Cristóbal   74
Let's Stop N Shop                        Jaime Yorres                   San Francisco   75
Comércio Mineiro                         Pedro Afonso                   Sao Paulo       78
Familia Arquibaldo                       Aria Cruz                      Sao Paulo       77
Queen Cozinha                            Lúcia Carvalho                 Sao Paulo       76
Tradição Hipermercados                   Anabela Domingues              Sao Paulo       79
White Clover Markets                     Karl Jablonski                 Seattle         80
Godos Cocina Típica                      José Pedro Freyre              Sevilla         81
Santé Gourmet                            Jonas Bergulfsen               Stavern         82
Blondesddsl père et fils                 Frédérique Citeaux             Strasbourg      83
Die Wandernde Kuh                        Rita Müller                    Stuttgart       84
Franchi S.p.A.                           Paolo Accorti                  Torino          85
La maison d'Asie                         Annette Roulet                 Toulouse        86
Bottom-Dollar Markets                    Elizabeth Lincoln              Tsawassen       87
Laughing Bacchus Wine Cellars            Yoshi Tannamuri                Vancouver       88
La corne d'abondance                     Daniel Tonini                  Versailles      89
Lazy K Kountry Store                     John Steel                     Walla Walla     90
Wolski  Zajazd                           Zbyszek Piestrzeniewicz        Warszawa        91

(91 row(s) affected)

 

RANK()

返回over后面order by子句的排名,相同排名拥有相同号码,后面的数会根据重复个数跳过.

 

select CompanyName, ContactName, City,
        RANK() over (order by City) as RankNum
from Customers
order by City

 

 结果:

 

CompanyName                              ContactName                    City            RankNum
---------------------------------------- ------------------------------ --------------- --------------------
Drachenblut Delikatessen                 Sven Ottlieb                   Aachen          1
Rattlesnake Canyon Grocery               Paula Wilson                   Albuquerque     2
Old World Delicatessen                   Rene Phillips                  Anchorage       3
Vaffeljernet                             Palle Ibsen                    Århus           4
Galería del gastrónomo                   Eduardo Saavedra               Barcelona       5
LILA-Supermercado                        Carlos González                Barquisimeto    6
Magazzini Alimentari Riuniti             Giovanni Rovelli               Bergamo         7
Alfreds Futterkiste                      Maria Anders                   Berlin          8
Chop-suey Chinese                        Yang Wang                      Bern            9
Save-a-lot Markets                       Jose Pavarotti                 Boise           10
Folk och fä HB                           Maria Larsson                  Bräcke          11
Königlich Essen                          Philip Cramer                  Brandenburg     12
Maison Dewey                             Catherine Dewey                Bruxelles       13
Océano Atlántico Ltda.                   Yvonne Moncada                 Buenos Aires    14
Rancho grande                            Sergio Gutiérrez               Buenos Aires    14
Cactus Comidas para llevar               Patricio Simpson               Buenos Aires    14
The Cracker Box                          Liu Wong                       Butte           17
Gourmet Lanchonetes                      André Fonseca                  Campinas        18
GROSELLA-Restaurante                     Manuel Pereira                 Caracas         19
Suprêmes délices                         Pascale Cartrain               Charleroi       20
Hungry Owl All-Night Grocers             Patricia McKenna               Cork            21
Island Trading                           Helen Bennett                  Cowes           22
QUICK-Stop                               Horst Kloss                    Cunewalde       23
Hungry Coyote Import Store               Yoshi Latimer                  Elgin           24
Great Lakes Food Market                  Howard Snyder                  Eugene          25
Lehmanns Marktstand                      Renate Messner                 Frankfurt a.M.  26
Richter Supermarkt                       Michael Holz                   Genève          27
Ernst Handel                             Roland Mendel                  Graz            28
Wilman Kala                              Matti Karttunen                Helsinki        29
LINO-Delicateses                         Felipe Izquierdo               I. de Margarita 30
Trail's Head Gourmet Provisioners        Helvetius Nagy                 Kirkland        31
Simons bistro                            Jytte Petersen                 Kobenhavn       32
Ottilies Käseladen                       Henriette Pfalzheim            Köln            33
Split Rail Beer & Ale                    Art Braunschweiger             Lander          34
Morgenstern Gesundkost                   Alexander Feuer                Leipzig         35
Folies gourmandes                        Martine Rancé                  Lille           36
Furia Bacalhau e Frutos do Mar           Lino Rodriguez                 Lisboa          37
Princesa Isabel Vinhos                   Isabel de Castro               Lisboa          37
Seven Seas Imports                       Hari Kumar                     London          39
North/South                              Simon Crowther                 London          39
Eastern Connection                       Ann Devon                      London          39
B's Beverages                            Victoria Ashworth              London          39
Consolidated Holdings                    Elizabeth Brown                London          39
Around the Horn                          Thomas Hardy                   London          39
Berglunds snabbköp                       Christina Berglund             Luleå           45
Victuailles en stock                     Mary Saveley                   Lyon            46
Bólido Comidas preparadas                Martín Sommer                  Madrid          47
FISSA Fabrica Inter. Salchichas S.A.     Diego Roel                     Madrid          47
Romero y tomillo                         Alejandra Camino               Madrid          47
Blauer See Delikatessen                  Hanna Moos                     Mannheim        50
Bon app'                                 Laurence Lebihan               Marseille       51
Ana Trujillo Emparedados y helados       Ana Trujillo                   México D.F.     52
Antonio Moreno Taquería                  Antonio Moreno                 México D.F.     52
Centro comercial Moctezuma               Francisco Chang                México D.F.     52
Pericles Comidas clásicas                Guillermo Fernández            México D.F.     52
Tortuga Restaurante                      Miguel Angel Paolino           México D.F.     52
Mère Paillarde                           Jean Fresnière                 Montréal        57
Frankenversand                           Peter Franken                  München         58
Toms Spezialitäten                       Karin Josephs                  Münster         59
France restauration                      Carine Schmitt                 Nantes          60
Du monde entier                          Janine Labrune                 Nantes          60
Wartian Herkku                           Pirkko Koskitalo               Oulu            62
Paris spécialités                        Marie Bertrand                 Paris           63
Spécialités du monde                     Dominique Perrier              Paris           63
Lonesome Pine Restaurant                 Fran Wilson                    Portland        65
The Big Cheese                           Liz Nixon                      Portland        65
Reggiani Caseifici                       Maurizio Moroni                Reggio Emilia   67
Vins et alcools Chevalier                Paul Henriot                   Reims           68
Wellington Importadora                   Paula Parente                  Resende         69
Ricardo Adocicados                       Janete Limeira                 Rio de Janeiro  70
Que Delícia                              Bernardo Batista               Rio de Janeiro  70
Hanari Carnes                            Mario Pontes                   Rio de Janeiro  70
Piccolo und mehr                         Georg Pipps                    Salzburg        73
HILARION-Abastos                         Carlos Hernández               San Cristóbal   74
Let's Stop N Shop                        Jaime Yorres                   San Francisco   75
Queen Cozinha                            Lúcia Carvalho                 Sao Paulo       76
Familia Arquibaldo                       Aria Cruz                      Sao Paulo       76
Comércio Mineiro                         Pedro Afonso                   Sao Paulo       76
Tradição Hipermercados                   Anabela Domingues              Sao Paulo       76
White Clover Markets                     Karl Jablonski                 Seattle         80
Godos Cocina Típica                      José Pedro Freyre              Sevilla         81
Santé Gourmet                            Jonas Bergulfsen               Stavern         82
Blondesddsl père et fils                 Frédérique Citeaux             Strasbourg      83
Die Wandernde Kuh                        Rita Müller                    Stuttgart       84
Franchi S.p.A.                           Paolo Accorti                  Torino          85
La maison d'Asie                         Annette Roulet                 Toulouse        86
Bottom-Dollar Markets                    Elizabeth Lincoln              Tsawassen       87
Laughing Bacchus Wine Cellars            Yoshi Tannamuri                Vancouver       88
La corne d'abondance                     Daniel Tonini                  Versailles      89
Lazy K Kountry Store                     John Steel                     Walla Walla     90
Wolski  Zajazd                           Zbyszek Piestrzeniewicz        Warszawa        91

(91 row(s) affected)

 

DENSE_RANK()

返回over后面order by子句的排名,相同排名拥有相同号码,后面的数会不会根据重复个数跳过.

select CompanyName, ContactName, City,
        DENSE_RANK() over (order by City) as RankNum
from Customers
order by City

 

结果:

 

CompanyName                              ContactName                    City            RankNum
---------------------------------------- ------------------------------ --------------- --------------------
Drachenblut Delikatessen                 Sven Ottlieb                   Aachen          1
Rattlesnake Canyon Grocery               Paula Wilson                   Albuquerque     2
Old World Delicatessen                   Rene Phillips                  Anchorage       3
Vaffeljernet                             Palle Ibsen                    Århus           4
Galería del gastrónomo                   Eduardo Saavedra               Barcelona       5
LILA-Supermercado                        Carlos González                Barquisimeto    6
Magazzini Alimentari Riuniti             Giovanni Rovelli               Bergamo         7
Alfreds Futterkiste                      Maria Anders                   Berlin          8
Chop-suey Chinese                        Yang Wang                      Bern            9
Save-a-lot Markets                       Jose Pavarotti                 Boise           10
Folk och fä HB                           Maria Larsson                  Bräcke          11
Königlich Essen                          Philip Cramer                  Brandenburg     12
Maison Dewey                             Catherine Dewey                Bruxelles       13
Océano Atlántico Ltda.                   Yvonne Moncada                 Buenos Aires    14
Rancho grande                            Sergio Gutiérrez               Buenos Aires    14
Cactus Comidas para llevar               Patricio Simpson               Buenos Aires    14
The Cracker Box                          Liu Wong                       Butte           15
Gourmet Lanchonetes                      André Fonseca                  Campinas        16
GROSELLA-Restaurante                     Manuel Pereira                 Caracas         17
Suprêmes délices                         Pascale Cartrain               Charleroi       18
Hungry Owl All-Night Grocers             Patricia McKenna               Cork            19
Island Trading                           Helen Bennett                  Cowes           20
QUICK-Stop                               Horst Kloss                    Cunewalde       21
Hungry Coyote Import Store               Yoshi Latimer                  Elgin           22
Great Lakes Food Market                  Howard Snyder                  Eugene          23
Lehmanns Marktstand                      Renate Messner                 Frankfurt a.M.  24
Richter Supermarkt                       Michael Holz                   Genève          25
Ernst Handel                             Roland Mendel                  Graz            26
Wilman Kala                              Matti Karttunen                Helsinki        27
LINO-Delicateses                         Felipe Izquierdo               I. de Margarita 28
Trail's Head Gourmet Provisioners        Helvetius Nagy                 Kirkland        29
Simons bistro                            Jytte Petersen                 Kobenhavn       30
Ottilies Käseladen                       Henriette Pfalzheim            Köln            31
Split Rail Beer & Ale                    Art Braunschweiger             Lander          32
Morgenstern Gesundkost                   Alexander Feuer                Leipzig         33
Folies gourmandes                        Martine Rancé                  Lille           34
Furia Bacalhau e Frutos do Mar           Lino Rodriguez                 Lisboa          35
Princesa Isabel Vinhos                   Isabel de Castro               Lisboa          35
Seven Seas Imports                       Hari Kumar                     London          36
North/South                              Simon Crowther                 London          36
Eastern Connection                       Ann Devon                      London          36
B's Beverages                            Victoria Ashworth              London          36
Consolidated Holdings                    Elizabeth Brown                London          36
Around the Horn                          Thomas Hardy                   London          36
Berglunds snabbköp                       Christina Berglund             Luleå           37
Victuailles en stock                     Mary Saveley                   Lyon            38
Bólido Comidas preparadas                Martín Sommer                  Madrid          39
FISSA Fabrica Inter. Salchichas S.A.     Diego Roel                     Madrid          39
Romero y tomillo                         Alejandra Camino               Madrid          39
Blauer See Delikatessen                  Hanna Moos                     Mannheim        40
Bon app'                                 Laurence Lebihan               Marseille       41
Ana Trujillo Emparedados y helados       Ana Trujillo                   México D.F.     42
Antonio Moreno Taquería                  Antonio Moreno                 México D.F.     42
Centro comercial Moctezuma               Francisco Chang                México D.F.     42
Pericles Comidas clásicas                Guillermo Fernández            México D.F.     42
Tortuga Restaurante                      Miguel Angel Paolino           México D.F.     42
Mère Paillarde                           Jean Fresnière                 Montréal        43
Frankenversand                           Peter Franken                  München         44
Toms Spezialitäten                       Karin Josephs                  Münster         45
France restauration                      Carine Schmitt                 Nantes          46
Du monde entier                          Janine Labrune                 Nantes          46
Wartian Herkku                           Pirkko Koskitalo               Oulu            47
Paris spécialités                        Marie Bertrand                 Paris           48
Spécialités du monde                     Dominique Perrier              Paris           48
Lonesome Pine Restaurant                 Fran Wilson                    Portland        49
The Big Cheese                           Liz Nixon                      Portland        49
Reggiani Caseifici                       Maurizio Moroni                Reggio Emilia   50
Vins et alcools Chevalier                Paul Henriot                   Reims           51
Wellington Importadora                   Paula Parente                  Resende         52
Ricardo Adocicados                       Janete Limeira                 Rio de Janeiro  53
Que Delícia                              Bernardo Batista               Rio de Janeiro  53
Hanari Carnes                            Mario Pontes                   Rio de Janeiro  53
Piccolo und mehr                         Georg Pipps                    Salzburg        54
HILARION-Abastos                         Carlos Hernández               San Cristóbal   55
Let's Stop N Shop                        Jaime Yorres                   San Francisco   56
Queen Cozinha                            Lúcia Carvalho                 Sao Paulo       57
Familia Arquibaldo                       Aria Cruz                      Sao Paulo       57
Comércio Mineiro                         Pedro Afonso                   Sao Paulo       57
Tradição Hipermercados                   Anabela Domingues              Sao Paulo       57
White Clover Markets                     Karl Jablonski                 Seattle         58
Godos Cocina Típica                      José Pedro Freyre              Sevilla         59
Santé Gourmet                            Jonas Bergulfsen               Stavern         60
Blondesddsl père et fils                 Frédérique Citeaux             Strasbourg      61
Die Wandernde Kuh                        Rita Müller                    Stuttgart       62
Franchi S.p.A.                           Paolo Accorti                  Torino          63
La maison d'Asie                         Annette Roulet                 Toulouse        64
Bottom-Dollar Markets                    Elizabeth Lincoln              Tsawassen       65
Laughing Bacchus Wine Cellars            Yoshi Tannamuri                Vancouver       66
La corne d'abondance                     Daniel Tonini                  Versailles      67
Lazy K Kountry Store                     John Steel                     Walla Walla     68
Wolski  Zajazd                           Zbyszek Piestrzeniewicz        Warszawa        69

(91 row(s) affected)