我有一个具有不同部门的示例员工表，现在该场景需要在所有列的员工表中显示最高重复的部门 [英] I have a sample employee table with distinct departments now the scenario is need to display the highest repeated departments in a employee table with all columns

问题描述

-->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.

推荐答案

分组依据不是所有记录 - 它是一个聚合函数。

见这里： SQL GROUP By和列'名称'无效选择列表因为......错误 [ ^ ]并解释它。

可能，你必须使用GROUP BY和聚合函数像COUNT和MAX以及JOIN一样选择你想要的记录。

首先编写GROUP by以返回你需要的部门名称 - 这很简单 - 然后将其添加到JOIN以获取你想要的行。

Group by isn't for "all records" - it's an aggregation function.
See here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] and it explains it.
Probably, you'll have to use a GROUP BY with aggregation functions like COUNT and MAX together with a JOIN to select the records you want.
Start by writing the GROUP by to return the department names you need - that's pretty simple - then add that to a JOIN to fetch the rows you want.

假设Microsoft SQL Server，这样的东西应该工作：

Assuming Microsoft SQL Server, something like this should work:

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

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