英文:
SQL using count and max functions at the same time
问题
我要翻译的内容：
I have a query and it brings me how many people live in which country.
Select Country, COUNT(EmployeeID) from Employees group by Country
What kind of query should I use if I want to get the most populated city and the number of people living in it?
The code I tried :
Select Country, MAX(COUNT(EmployeeID)) from Employees group by Country
翻译后的内容：
我有一个查询，它告诉我每个国家有多少人居住。
选择 国家, 计数(员工ID) 从 员工表 分组按 国家
如果我想要获取人口最多的城市以及居住在其中的人数，我应该使用什么样的查询？
我尝试的代码：
选择 国家, MAX(计数(员工ID)) 从 员工表 分组按 国家
英文:
I have a query and it brings me how many people live in which country.
Select Country, COUNT(EmployeeID) from Employees group by Country
What kind of query should I use if I want to get the most populated city and the number of people living in it?
The code I tried :
Select Country, MAX(COUNT(EmployeeID)) from Employees group by Country
答案1
得分: 4
你可以使用 TOP：
选择 TOP 1 Country, COUNT(EmployeeID) as [count]
从 Employees 表中
按 Country 分组
按 [count] 降序排序
英文:
You can use TOP :
Select TOP 1 Country, COUNT(EmployeeID) as [count]
from Employees
group by Country
order by [count] desc
答案2
得分: 0
尝试这个。qty - 按国家计数，maxqty - 所有国家的最大计数。
选择 Country
,COUNT(EmployeeID) qty
,MAX(COUNT(EmployeeID))over() maxqty
来自 Employees
根据 Country 分组
对于测试数据
创建表 Employees (EmployeeId int, country varchar(20));
插入到 Employees 中的值
(1,'country1'),(2,'country1')
,(3,'country2'),(4,'country2'),(5,'country2')
;
结果
| Country | qty | maxqty |
|---|---|---|
| country1 | 2 | 3 |
| country2 | 3 | 3 |
英文:
Try this. qty - count for country, maxqty - max count for all country.
Select Country
,COUNT(EmployeeID) qty
,MAX(COUNT(EmployeeID))over() maxqty
from Employees
group by Country
For test data
create table Employees (EmployeeId int,country varchar(20));
insert into Employees values
(1,'country1'),(2,'country1')
,(3,'country2'),(4,'country2'),(5,'country2')
;
Result
| Country | qty | maxqty |
|---|---|---|
| country1 | 2 | 3 |
| country2 | 3 | 3 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论