获取所有列的最常见值

I want to get the most common values for all columns in a mySQL table.

我想获取mySQL表中所有列的最常见值。

[id]  [UserID]            [UserID2]
1     johnnietheblack     marywhite
2     johnnietheblack     marywhite
3     dannyrottenegg      dannyrottenegg     
4     marywhite           marywhite
5     marywhite           johnnietheblack     
6     johnnietheblack     marywhite

Here's the output I want:

这是我想要的输出:

[id]  [UserID]            [Count]
    1     johnnietheblack     4
    2     dannyrottenegg      2
    3     marywhite           6

I can use the following to get the common values for one columns. But how can I get the common values for all columns?

我可以使用以下内容来获取一列的常用值。但是如何获得所有列的公共值?

SELECT COUNT(*) AS `Rows`, UserID
FROM table-name
GROUP BY UserID
ORDER BY `Rows` DESC

1 个解决方案

#1

SELECT UserID, COUNT(*) FROM
(SELECT UserID FROM tablename
 UNION ALL
 SELECT UserID2 FROM tablename) t
GROUP BY UserID

I didn't include the id column because it doesn't seem to correlate with the values.

我没有包含id列,因为它似乎与值没有关联。

If you want to see all of the ids for each name, you can use GROUP_CONCAT:

如果要查看每个名称的所有ID,可以使用GROUP_CONCAT:

SELECT GROUP_CONCAT(id), UserID, COUNT(*) FROM
(SELECT id, UserID FROM tablename
 UNION ALL
 SELECT id, UserID2 FROM tablename) t
GROUP BY UserID