获取所有列的最常用值 [英] Get the most common values for all columns

问题描述

我想获取mySQL表中所有列的最通用值.

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

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

这是我想要的输出:

[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

推荐答案

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

我没有包括id列，因为它似乎与值不相关.

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

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

If you want to see all of the ids for each name, you can use 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

这篇关于获取所有列的最常用值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！