我有一个具有不同部门的示例员工表，现在该场景需要在所有列的员工表中显示最高重复的部门

问题描述

-->INPUT:
empid department  names    salary
1	dotnet	    klrahul	     1000
2	dotnet	    dhawan	     2000
3	dotnet	    virat	     1500
4	design	    dhoni	     3000
5	design	    karthik	     2000
6	design	    phant	     3040
7	php	        chawal	     4000
8	php	        kpandya	     2000
9	php	        skaual	     4300
10	php	        bhumra	     2000
11	dotnet	    vijay	     1646

-->OUTPUT:
empid   department  names   salary
1	dotnet	    klrahul	     1000
2	dotnet	    dhawan	     2000
3	dotnet	    virat	     1500
11	dotnet	    vijay	     1646
7	php	        chawal	     4000
8	php	        kpandya	     2000
9	php	        skaual	     4300
10	php	        bhumra	     2000

我的尝试：



我试过group by having子句但我无法获取所有列，任何人都可以帮助我使用SQL Server 2012查询。

What I have tried:

I tried group by having clause but i am unable to fetch all the columns can anyone help me with a SQL Server 2012 query.

推荐答案

SELECT TOP 1 WITH TIES
    empid,
    department,
    names,
    salary
FROM
    YourTable
ORDER BY
    COUNT(1) OVER (PARTITION BY department) DESC
;

这篇关于我有一个具有不同部门的示例员工表，现在该场景需要在所有列的员工表中显示最高重复的部门的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！