(My)SQL-计算表中不同列中的值 [英] (My)SQL - Count values from table in different columns

问题描述

我需要获取表中每个值的计数.问题是我需要从一张表的3列中取出它.

I need to get the count of each value in a table. Problem is I need to get it out of 3 columns in that one table.

(简化的)数据库表如下所示:

The (simplified) database table would look something like this:

+---------+------+--------+------+
|   Id    | Col1 | Col2   | Col3 | 
+---------+------+--------+------+
|    1    |  a   |  a     |      |
|    2    |  b   | null   |  a   |
|    3    |  b   |  b     |  c   |
|    4    |  d   |  a     |  null|
|    5    |  a   |  c     |  c   |
+---------+------+--------+------+

这就是我需要的结果:

+-------+-------+
|  Col  | Count |
+-------+-------+
|   a   |   5   |
|   b   |   3   |
|   c   |   3   |
|   d   |   1   |
+-------+-------+

有什么建议吗?

我还想获得具有最高计数的x量行的顶部，即使必须按asc/desc对计数或col进行排序，也必须显示10 HIGHEST.我发现按Count desc订购时，简单的限制10是行不通的.然后我会得到10个最低值，而不是最高值.

I also want to get the top of x-amount of rows with the highest count, even when doing an order by asc/desc on either count or col the 10 HIGHEST must be shown. I figured out that a simple limit 10 wouldn't work when I order by Count desc. Then I would get the 10 lowest values instead of the highest.

推荐答案

最简单的可能是带有GROUP BY;的UNION ALL;

Simplest is probably a UNION ALL with a GROUP BY;

SELECT col, COUNT(*) count
FROM (
  SELECT col1 col FROM mytable UNION ALL 
  SELECT col2 col FROM mytable UNION ALL 
  SELECT col3 col FROM mytable
)z
GROUP BY col
HAVING col IS NOT NULL
ORDER BY count DESC
LIMIT 10

要测试的SQLfiddle .

如果您希望将其限制在前10名，但又订购了其他方向，则您可以将其包装起来在子查询中并排序外部查询.

If you want it limited to the top 10 but ordered the other direction, you can just wrap it in a subquery and order the outer one.

这篇关于(My)SQL-计算表中不同列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！