限制表中一列的行数

Question

我有一个类似的表

MyTable (id, name, counter, createDate)

我可以像一样订购查询

Select * from MyTable order by counter;

我只是想知道，是否可以限制每天的行数。例如，我想要上面查询中的行，但不希望每天的行数超过10行。因此，如果它有100天的输入时间，我将寻找小于或等于10(行(x100(天(行的数据。

我真的不太擅长SQL。任何帮助/建议都将不胜感激。

Answer 1

每天10行，counter:最少

WITH cte AS ( SELECT *, ROW_NUMBER OVER (PARTITION BY createDate 
ORDER BY counter) rn
FROM MyTable )
SELECT *
FROM cte
WHERE rn <= 10

如果createDate具有DATETIME/TIMESTAMP数据类型，则

WITH cte AS ( SELECT *, ROW_NUMBER OVER (PARTITION BY DATE(createDate) 
ORDER BY counter) rn
FROM MyTable )
SELECT *
FROM cte
WHERE rn <= 10

Answer 2

SELECT * FROM (SELECT row_number() OVER (PARTITION BY createDate ORDER BY createDate) r, c.* FROM MyTable T c) WHERE r <= 10