sql - 我怎样才能只找到较少的计数值 Name Must want 3 groupName 也

Question

在 SQL (adventureWorks) 数据库中...

I/P :
ALTER PROCEDURE spLessDep
AS
BEGIN
SELECT COUNT(Name) AS COUNT,max(GroupName)
FROM HumanResources.Department
WHERE GroupName <(SELECT MAX(GroupName) FROM HumanResources.Department)
GROUP BY GroupName
ORDER BY COUNT(Name)
END

EXEC spLessDep

输出/输出：

Name   GroupName

2      Inventory Management
2      Manufacturing
2      Quality Assurance
3      Research and Development
5      Executive General and Administration

要求：我想通过唯一的语句在列（名称）中的所有最小值

标签： sqlsql-servermssql-jdbc

Answer 1

我想你在描述TOP (1) WITH TIES：

SELECT TOP (1) WITH TIES COUNT(Name) AS COUNT, GroupName
FROM HumanResources.Department
WHERE GroupName < (SELECT MAX(GroupName) FROM HumanResources.Department)
GROUP BY GroupName
ORDER BY COUNT(Name);