SQL Server 2016查询前50条且同公司最多返回10条的实现方案

你原有代码存在过滤条件写反的问题：你需要筛选状态为空的待处理记录，原代码where insull(Status,'') <> ''实际取的是状态非空的已处理记录，需要修正过滤逻辑。
要实现单日查询同公司最多10条的限制，可以用ROW_NUMBER()窗口函数实现分组排序过滤，具体代码如下：

WITH RankedEmployee AS (
    SELECT 
        *,
        -- 按公司分组，同公司内按提交时间升序排序编号
        ROW_NUMBER() OVER (PARTITION BY Company ORDER BY [DateTime] ASC) AS CompanyRank
    FROM EmployeeData
    WHERE ISNULL(Status, '') = '' -- 筛选状态为空的待处理记录
)
SELECT TOP 50 *
FROM RankedEmployee
WHERE CompanyRank <= 10 -- 限制单个公司最多取10条
ORDER BY [DateTime] ASC -- 全局取最早提交的50条

如果需要直接更新查询出来的50条记录的状态，可直接基于CTE执行更新，避免二次查询匹配：

WITH RankedEmployee AS (
    SELECT 
        Status,
        [DateTime],
        ROW_NUMBER() OVER (PARTITION BY Company ORDER BY [DateTime] ASC) AS CompanyRank
    FROM EmployeeData
    WHERE ISNULL(Status, '') = ''
),
Top50ToProcess AS (
    SELECT TOP 50 Status
    FROM RankedEmployee
    WHERE CompanyRank <= 10
    ORDER BY [DateTime] ASC
)
UPDATE Top50ToProcess
SET Status = '已处理' -- 替换为实际业务的状态取值即可

内容的提问来源于stack exchange，提问作者ValiantSirDK