One table, two column MYSQL query challenge
Consider a MYSQL table called "comments" with 2 columns "hostname" and "username".
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?
If I do
SELECT hostname, count(distinct(username))
FROM comments
GROUP BY hostname
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
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
You have to add a new user-defined aggregate function, which concatenates the collected username strings.
I would give a shot at this: http://www.codeproject.com/KB/database/mygroupconcat.aspx
SELECT HOSTNAME,
RTRIM(
REPLACE(
REPLACE(XMLAGG(XMLELEMENT("x", USERNAME)).getstringval(), '<x>', NULL),
'</x>',
','
),
','
) LIST_OF_USERNAMES
FROM COMMENTS
GROUP BY HOSTNAME;
Example:
If the source table is:
HOSTNAME USERNAME
10.12.110.80 GOOFY
10.12.110.80 MINNIE
10.12.110.81 STAR
10.12.110.81 MOON
10.12.110.81 SUN
10.12.110.82 MARMELADE
10.12.110.82 ORANGE
10.12.110.82 JUICE
10.12.110.82 LEMON
the output will be the following:
HOSTNAME LIST_OF_USERNAMES
10.12.110.80 GOOFY,MINNIE
10.12.110.81 STAR,MOON,SUN
10.12.110.82 MARMELADE,ORANGE,JUICE,LEMON
It works fine in Oracle 10G R2.
链接地址: http://www.djcxy.com/p/30512.html下一篇: 一个表，两列MYSQL查询挑战