一表两列MYSQL查询挑战 [英] One table, two column MYSQL query challenge

问题描述

考虑一个名为"comments"的MYSQL表，该表有两列"hostname"和"username".

Consider a MYSQL table called "comments" with 2 columns "hostname" and "username".

如何返回按主机名排序的列表，其中第二列是与每个主机名关联的用户名列表?我可以看到如何使用像py这样的脚本语言来做到这一点，但是有可能作为标准SQL查询吗?

How do I return a list sorted by hostname where the 2nd column is a list of usernames associated with each hostname? I can see how to do this with a scripting language like py, but is it possible as a standard SQL query?

如果我这样做

SELECT hostname, count(distinct(username)) 
FROM comments 
GROUP BY hostname

我得到的结果几乎是正确的，除了它只给我与每个主机名关联的用户名数量，而不是与每个主机名关联的用户名的实际列表...尝试不区分用户名(用户名)而返回count()返回语法错误.

I get almost the right result, except It only gives me the number of usernames associated with each hostname instead of the actual list of usernames associated with each hostname... trying distinct(username) without the count() around it returns a syntax error.

推荐答案

我认为最简单的方法是选择不同的行，并在必要时在客户端中重新格式化它:

I think the cleanest way to do this is to just select distinct rows and reformat it in the client if necessary:

SELECT DISTINCT hostname, username
FROM comments
ORDER BY hostname, username

或者，如果您希望将结果连接起来，则可以使用 GROUP_CONCAT :

Alternatively if you want the results concatenated you can use GROUP_CONCAT:

SELECT hostname, GROUP_CONCAT(DISTINCT username) as usernames
FROM comments
GROUP BY hostname

这篇关于一表两列MYSQL查询挑战的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！