比如,在下面的销售业绩中,统计业务员的销售业绩中最大值和最小值。
下面是业务数据:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
填充数据:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
INSERT INTO [dbo].[SalesPerformance] ([Salesman],[OrderDate],[Sell]) VALUES ('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) GO
使用FIRST_VALUE和LAST_VALUE函数进行分组查询:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SELECT [ID],[Salesman],[OrderDate],[Sell], FIRST_VALUE([Sell]) OVER (PARTITION BY [Salesman] ORDER BY [Sell]) [最低销售额], LAST_VALUE([Sell]) OVER (PARTITION BY [Salesman] ORDER BY [Sell]) [最高销售额] FROM [dbo].[SalesPerformance]