获取所有具有最小值SQL Server的项目

Question

这就是表的样子：

----------------------------------
   EmployeeId      Tasks_Count
   1               1
   2               1
   3               2
   4               1
   5               3

我需要查询以使所有员工都有最小的任务数。结果应该是这样：

---------------
   EmployeeId   
   1               
   2               
   4

问题是我使用子查询来计算任务。这是我的代码

SELECT *
FROM (SELECT EmployeeId,
             COUNT(*) AS Tasks_count
      FROM Tasks
      INNER JOIN Status ON Tasks.StatusId=Status.Id
      WHERE Status.Name != 'Closed'
      GROUP BY EmployeeId
      ORDER BY Tasks_count DESC) AS Employee_not_closed
WHERE Tasks_count IN (SELECT MIN(Tasks_count) 
                      FROM Employee_not_closed)

Answer 1

使用 FETCH FIRST WITH TIES：

select EmployeeId
from tablename
order by Tasks_Count
fetch first 1 row with ties

Answer 2

您可以在下面尝试 -

select * from tablename
where Tasks_Count in (select min(Tasks_Count) from tablename)

Answer 3

也可以使用RANK()函数进行以下操作。

;with cte as
(
   select Employeeid, rank() over( order by Tasks_Count) rn
   from @table
)
select * from cte where rn=1

Answer 4

您可以使用以下代码我测试了代码及其工作正常。

select EmployeeId from StackOverFlow_3 where Tasks_Count in(select min(Tasks_Count) from StackOverFlow_3)

Answer 5

您可以在子查询上使用JOIN

    select m.EmployeeId 
    from my_table  m
    inner join  
    (
        select min(task_count) min_task
        from my_table 
    ) t  on t.min_task = m.task_count