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