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生成的数据.