计算多列中的出现次数 [英] Count the occurrences across multiple columns

问题描述

我正在尝试计算表中多个列的出现次数.所有值都是整数，所有数字在每一行中都是唯一的. (即您不会在同一行中出现两个值)表结构为:

Im trying to count occurrences across multiple columns in a table. All values are integers and all the numbers are unique in each row. (i.e you won't get two values occurring in the same row) The table structure is:

ID  Number1  Number2  Number3  Number4  Number5  Number6
---------------------------------------------------------
    11       6        4        5        9        8
    6        9        11       5        3        15
    8        5        9        11       4        6
    4        11       17       3        7        1

预期输出为:

Number  Count
--------------
11      4
6       3
4       3
5       3
9       3
3       2
8       2
15      1
17      1
7       1
1       1

我尝试使用Internet上的数据透视和其他各种方法，但似乎无法使其正常工作.任何想法，似​​乎都是一个简单的查询，但是我做对了.

I've tried using pivots and various other methods found on the internet but just can't seem to get it working correctly. Any ideas, it seems like a simple query but I just can't get it right.

推荐答案

如果只有6列，则可以采用一种方法

If there are only 6 columns, then one way you can do this

select Number, count(*) From (select Number1 as Number from your_table
union all
select Number2 as Number from your_table
union all
select Number3 as Number from your_table
union all
select Number4  as Number from your_table
union all
select Number5  as Number from your_table
union all
select Number6 as Number from your_table) myTab
group by Number;

这篇关于计算多列中的出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！