Question

统计每行数据每个数字出现次数的SQL语句怎么写？

比如下面的5行数据，每行数据都单独的在ABCDE5列中
ABCDE
12345
39456
12374
78963
28374

要怎么统计1-9这9个数字里每个数字出现的次数？
比如1出现了2次，3出现了3次，执行语句，出现结果。
num count
1 2
3 3

Answer 1

declare @one int = 0

select @one = @one + 1 from table1
where A = 1

select @one = @one + 1 from table1
where B = 1

select @one = @one + 1 from table1
where C = 1

select @one = @one + 1 from table1
where D = 1

select @one = @one + 1 from table1
where E = 1

select 1 NO,@one CT into #Test

select * from #Test

这只是一种方法而已，我也不是很资深，只能将问题简化，多写sql
还有一种就是将所有的列的数字全部当作一列处理

select Tb.A,count(Tb.A) from (
select A 'A' from table1
union all
select B from table1
union all
select C from table1
……
) Tb
group by Tb.A

因为没有在数据库检测，所以不能保证语句正确，但是思路是正确的