查找曾在最大公司工作的用户 [英] To find user who having worked in the maximum number company

问题描述

编写一个查询，以显示已按照名称排序的最大公司数量的校友用户（角色 - '校友'）的名称。

Write a query to display the name of the alumni user(s) (Role-'Alumni') who has/have worked in maximum number of companies, sorted by name.

profile_id  company_name
1           Tcs
2           cts
3           hcl
1           hexa
4           mindtree
3           cts
1           cts

所以答案应该是

so the answer should be

profile-id    company_count
    1             3

这个我的查询

This my query

select u.name,Count(distinct ex.company_name)
from user u
inner join role r on (r.id = u.role_id)
inner join experience ex on (ex.profile_id = u.profile_id)
where r.name ="Alumni"
group by u.name
--having Count(distinct ex.company_name) in (select max(ex.company_name) from experience ex where ex.profile_id = u.profile_id)
order by u.name

推荐答案

SELECT TOP 1 profile_id,COUNT(company_name) company_count FROM COMPANY GROUP BY profile_id ORDER BY MAX(company_name) DESC

您在查询中使用保留字用户作为表名。用户使用[用户]

这里解释了

http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name- in-mysql [ ^ ]



尝试这样：

You are using the Reserved word User as table name in Your query .insted of user use [user]
Here explained
http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql[^]

try like this:

select u.name,Count(distinct ex.company_name)
from [user] u
inner join role r on (r.id = u.role_id)
inner join experience ex on (ex.profile_id = u.profile_id)
where r.name ="Alumni"
group by u.name
--having Count(distinct ex.company_name) in (select max(ex.company_name) from experience ex where ex.profile_id = u.profile_id)
order by u.name 

。

.

这篇关于查找曾在最大公司工作的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！