选择50行，但同一列最多只能选择10行

Question

我们正在运行SQL Server 2016。我目前正在构建一个表，它将保存我们有业务往来的各个公司的员工数据。该表将以以下形式填充各个公司的员工信息:

DateTime11/08/2021 07:01:00.0011/08/2021 07:02:00.0011/08/2021 07:03:00.0011/08/2021 07:04:00.0011/08/2021 07:05:00.0011/08/2021 07:06:00.0011/08/2021 07:07:00.0011/08/2021 07:08:00.0011/08/2021 07:09:00.0011/08/2021 07:10:00.0011/08/2021 07:11:00.0011/08/2021 07:12:00.0011/08/2021 07:13:00.0011/08/2021 07:14:00.0011/08/2021 07:15:00.0011/08/2021 07:16:00.00

可能是这样的:

select top 50 [Name], [Company], [Status], [DateTime]
from (
SELECT [Name], [Company], [Status], [DateTime],
ROW_NUMBER ( ) OVER ( PARTITION BY [Company] order by [Name] ) rownum
FROM EmployeeData
where insull(Status,'') <> ''
) tbl
where rownum <= 10
order by [DateTime]

这个查询有两个CTE。首先，选择唯一的公司名称为"unq_cte"。其次，为每个公司随机选择10行作为"all_cte"。最后，从'all_cte'(每个公司最多10行的集合)中随机选择50行。

;with
unq_cte as (
select distinct Company
from EmployeeData),
all_cte as (
select top10.*
from unq_cte uc
cross apply (select top(10) *
from EmployeeData ed
where uc.Company=ed.Company
order by newid()) 
top10([Name], Company, [Status], [DateTime]))
select top(50) *
from all_cte
order by newid();