34,838
社区成员




一个表
商品编号 入柜id 日期
a00001 11 2022-4-1
a00002 11 2022-4-1
a00003 11 2022-4-1
a00004 11 2022-4-1
a00001 11 2022-4-2
a00001 11 2022-4-3
b00001 12 2022-4-1
要求:指定入柜id 按日期段查询,按入柜id分组,限定每天显示记录,比如不超过3条,期望如下(多余3条的按每个入柜id,随机商品编号3条记录)
a00002 11 2022-4-1
a00003 11 2022-4-1
a00004 11 2022-4-1
a00001 11 2022-4-2
a00001 11 2022-4-3
参考楼上的就可以
CREATE TABLE #T
(ITEMNO VARCHAR(10),
CTN_ID VARCHAR(10),
DT DATE)
INSERT INTO #T
SELECT 'A00001','11','2022-04-01' UNION ALL
SELECT 'A00002','11','2022-04-01' UNION ALL
SELECT 'A00003','11','2022-04-01' UNION ALL
SELECT 'A00004','11','2022-04-01' UNION ALL
SELECT 'A00001','11','2022-04-02' UNION ALL
SELECT 'A00001','11','2022-04-03' UNION ALL
SELECT 'B00001','12','2022-04-01'
SELECT *
FROM
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY CTN_ID,DT ORDER BY NEWID()) AS SEQ
FROM #T) AS A
WHERE SEQ<=3