冗余数据按出现次数取值的解决方案

create table test1 (A int(5) , B int(5) ,C int(5) ,D int(5) , E int(5));
insert into test1 values(3,2,7,5,6);
insert into test1 values(1,5,9,8,5);
insert into test1 values(5,8,9,8,6);
insert into test1 values(3,2,8,8,0);
insert into test1 values(3,9,6,5,7);

mySql， 行格式

with
result_a as (with a as (SELECT a , count(1) val from test1 group by a)
select a 'A' from a where val = (select max(val) from a) limit 1),

result_b as (with b as (SELECT b, count(1) val from test1 group by b)
select b 'B' from b where val = (select max(val) from b)limit 1),

result_c as (with c as (SELECT c, count(1) val from test1 group by c)
select c 'C' from c where val = (select max(val) from c)limit 1),

result_d as (with d as (SELECT d, count(1) val from test1 group by d)
select d 'D' from d where val = (select max(val) from d) limit 1),

result_e as (with e as (SELECT e, count(1) val from test1 group by e)
select e 'E' from e where val = (select max(val) from e)limit 1)

select * from result_a, result_b, result_c, result_d, result_e;

mySql， 列格式

with
result_a as (with a as (SELECT a , count(1) val from test1 group by a)
select 'a' field, a val from a where val = (select max(val) from a) limit 1),

result_b as (with b as (SELECT b, count(1) val from test1 group by b)
select 'b' field, b val from b where val = (select max(val) from b)limit 1),

result_c as (with c as (SELECT c, count(1) val from test1 group by c)
select 'c' field, c val from c where val = (select max(val) from c)limit 1),

result_d as (with d as (SELECT d, count(1) val from test1 group by d)
select 'd' field, d val from d where val = (select max(val) from d) limit 1),

result_e as (with e as (SELECT e, count(1) val from test1 group by e)
select 'e' field, e val from e where val = (select max(val) from e)limit 1)

select * from result_a
UNION
select * from result_b
UNION
select * from result_c
UNION
select * from result_d
UNION
select * from result_e

#select * from result_a, result_b, result_c, result_d, result_e;