我正在尝试计算表中多个列的出现次数。所有值都是整数,所有数字在每一行中都是唯一的。(即,您不会在同一行中出现两个值)表结构为:
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
我曾尝试使用枢轴和互联网上的其他各种方法,但似乎无法使其正常工作。任何想法,似乎是一个简单的查询,但我做对了。
如果只有6列,则可以采用一种方法
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;