如何编写查询以显示已回答大多数查询的用户的名称（按名称排序）。 [英] How do I write a query to display the name(s) of the user who has/have answered most number of queries, sorted by name.

问题描述

我没有得到所需的输出



我尝试过：



我使用的代码是



SELECT u.name

FROM user as u，query as q

WHERE u.id = q.user_id

GROUP BY u.name HAVING COUNT（q.user_id）= 2

ORDER BY u.name;

I'm not getting the desired output

What I have tried:

the code i have used is

SELECT u.name
FROM user as u, query as q
WHERE u.id = q.user_id
GROUP BY u.name HAVING COUNT(q.user_id) = 2
ORDER BY u.name;

推荐答案

您将需要提供有关所需输出的更多信息。你可能没有看到你想要的东西，因为你的查询中有一个HAVING子句。否则您的查询看起来可能是正确的。



请参阅下面的示例架构/数据

Your going to have to give a lot more information about what your desired output is. You are probably not seeing what you want as you've got a HAVING clause in your query. Otherwise your query looks like it is probably correct.

See example schema/data below

DECLARE @Users TABLE  (
	Id INT NULL,
	Username VARCHAR(200) NULL
);

DECLARE @Query TABLE (
	UserId INT NULL,
	Query VARCHAR(200) NULL
);

INSERT INTO @Users (Id,Username) VALUES (1,'user1')
INSERT INTO @Users (Id,Username) VALUES (2,'user2')

INSERT INTO @Query (UserId, Query) VALUES (1, 'A');
INSERT INTO @Query (UserId, Query) VALUES (2, 'A');
INSERT INTO @Query (UserId, Query) VALUES (2, 'A');
INSERT INTO @Query (UserId, Query) VALUES (2, 'A');
INSERT INTO @Query (UserId, Query) VALUES (2, 'A');


SELECT 
	A.Username,
	COUNT(*)
FROM @Users AS A
JOIN @Query AS B ON B.UserId = A.Id
GROUP BY A.Username
ORDER BY A.Username

然后输出的是

Then the output of this is

Username	ItemsAnswered
user1	            1
user2	            4

哪个是用户名订购的

Which is ordered by the username

SELECT u.name FROM user as u，query as q

WHERE u.id = q.user_id and q.parent_id is NOT NULL

GROUP BY u .name HAVING COUNT（q.user_id ）= 2

ORDER BY u.name;

SELECT u.name FROM user as u, query as q
WHERE u.id=q.user_id and q.parent_id is NOT NULL
GROUP BY u.name HAVING COUNT(q.user_id) = 2
ORDER BY u.name;

这篇关于如何编写查询以显示已回答大多数查询的用户的名称（按名称排序）。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！