SQL新函数, 排名函数 - ROW_NUMBER(), RANK(), DENSE_RANK()
ROW_NUMBER()
根据over后的order by字据的字段排序,返回一个不断递增的整数.
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子句的排名,相同排名拥有相同号码,后面的数会根据重复个数跳过.
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子句的排名,相同排名拥有相同号码,后面的数会不会根据重复个数跳过.
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)
