计算多列中的出现次数

本文介绍了计算多列中的出现次数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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;