SQL PARTITION BY 语句把一张表分组后的最大值或最小值插入另一张表里

SQL PARTITION BY的基础应用如上一篇所示：

1.例子见前一章，目的是有分组的，只显示OrderAmount最高的（即每组只显示一列）

 2.再建一个表来存储

CREATE TABLE [dbo].[MaxOrders](
    [orderid] [int] NULL,
    [Orderdate] [date] NULL,
    [CustomerName] [varchar](100) NULL,
    [Customercity] [varchar](100) NULL,
    [MaxOrderamount] [money] NULL
) ON [PRIMARY]
GO

3.往新表插入数据的写法如下：

insert into [dbo].[MaxOrders]
(Orderdate,CustomerName,Customercity,MaxOrderamount)
select t.Orderdate,t.CustomerName,t.Customercity,t.Orderamount
from (
select ROW_NUMBER() over (partition by Customercity order by Orderamount desc)as row_num, *
from Orders
) as t  where row_num=1

 4.把上面合起来写法如下（就是对已有数据进行初始化处理，一开始表要插入这些数据）：

IF NOT EXISTS(SELECT 1 FROM sys.tables where name='MaxOrders2')
BEGIN
CREATE TABLE [dbo].[MaxOrders2](
    [orderid] [int] NULL,
    [Orderdate] [date] NULL,
    [CustomerName] [varchar](100) NULL,
    [Customercity] [varchar](100) NULL,
    [MaxOrderamount] [money] NULL
) ON [PRIMARY]
DECLARE @sql2 nvarchar(2048)='
insert into [dbo].[MaxOrders2]
(orderid,Orderdate,CustomerName,Customercity,MaxOrderamount)
select t.orderid, t.Orderdate,t.CustomerName,t.Customercity,t.Orderamount
from (
select ROW_NUMBER() over (partition by Customercity order by Orderamount desc)as row_num, *
from Orders
) as t  where row_num=1
';
EXEC sys.sp_executesql @query=@sql2;
END