计总与排名SUM和RANK函数，sumrank函数
准备一些数据：
![]()
CREATE TABLE [dbo].
[SalesPerformance](
[ID] [int] IDENTITY(
1,
1)
NOT NULL,
[Salesman] NVARCHAR(
30)
NOT NULL,
[OrderDate] [DATE] NULL,
[Sell] DECIMAL(
18,
2)
NULL
)
GO
SELECT [Salesman],
[OrderDate],
[Sell] FROM [dbo].
[SalesPerformance]
GO
Source Code
![]()
Salesman OrderDate Sell
S0003 2019-05-12 23800.00
S0008 2019-05-19 66528.00
S0001 2019-05-05 35455.00
S0001 2019-05-18 75220.00
S0003 2019-05-17 33658.00
S0041 2019-05-10 56300.00
S0041 2019-05-11 41811.00
S0003 2019-05-20 26309.00
S0007 2019-05-02 41811.00
S0022 2019-05-26 26309.00
S0032 2019-05-20 20000.00
S0050 2019-05-28 20000.00
Data
使用SUM和GROUP BY统计各个业务员的销售额：
![]()
SELECT [Salesman] AS [业务员],
MONTH(
[OrderDate])
AS [月份],
SUM(
[Sell])
AS [销售量]
FROM [dbo].
[SalesPerformance]
GROUP BY [Salesman],
MONTH(
[OrderDate])
Source Code
然后使用RANK进行排名，看看谁是销售冠军，谁与谁同级：
![]()
;
WITH [QuantityOfSale] AS
(
SELECT [Salesman] AS [业务员],
MONTH(
[OrderDate])
AS [月份],
SUM(
[Sell])
AS [销售量]
FROM [dbo].
[SalesPerformance]
GROUP BY [Salesman],
MONTH(
[OrderDate])
)
SELECT [业务员],
[月份],
[销售量],RANK()
OVER(
ORDER BY [销售量] DESC)
[销售排名]
FROM [QuantityOfSale]
Source Code
http://www.htsjk.com/Sql_Server/24838.html
www.htsjk.Com
true
http://www.htsjk.com/Sql_Server/24838.html
NewsArticle
计总与排名SUM和RANK函数，sumrank函数 准备一些数据： CREATE TABLE [ dbo ] . [ SalesPerformance ] ( [ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , [ Salesman ] NVARCHAR ( 30 ) NOT NULL , [ OrderDate ] [ DATE ] NULL , [ Sell ]...
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。