SQL分组聚合查询练习(SQL Server和Oracle相似)20190514

先建表

CREATE TABLE [dbo].[orderdt_jimmy](

    [nid] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,

    [order_nid] [int] NOT NULL,

    [sku] [varchar](100) NOT NULL,

    [qty] [int] NOT NULL,

    [price] [decimal](12, 2) NULL,

    [amount] [decimal](12, 2) NULL

);

GO

怎么查询每一组的前几个?

select t1.rn,t1.order_nid,t1.sku,t1.qty,t1.price,t1.amount from(

select *,ROW_NUMBER() over(partition by order_nid order by sku) rn from orderdt_jimmy) t1

where t1.rn < 5

order by nid,rn;

go

此时,是以order_nid分组,sku排序,然后查询每一组的前四个,如果小于四个,查询数量不变.

效果图:

这些数据都是我自己随机生成的,是自己练习demo生成的数据.