SQL 取分组后前三条数据

CREATE TABLE TestSerialNumber
(
	id int identity(1000,1) primary key,
	name varchar(20),
	code as LEFT(CONVERT(VARCHAR,GETDATE(),120),10)+'-'+CAST(id AS VARCHAR)
)

INSERT INTO TestSerialNumber
SELECT 'A'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'C'

--可以根据 order by name 来控制排序规则
SELECT T.id,T.name,T.rowNumber FROM (
SELECT * ,row_number() over (partition by name order by name) as rowNumber 
FROM TestSerialNumber 
) T
GROUP BY T.id,T.name,T.rowNumber HAVING rowNumber<=3