获取表中前3个列的列名

Question

我需要一个表中的列名，这是具有最高计数的前三列。

我正在使用mySQL:

我可以使用这个查询获得所需列的计数:

SELECT 
COUNT(unsafe_spaces_home) AS AH ,
COUNT(unsafe_spaces_school) AS SCH ,
COUNT(unsafe_spaces_market_place) AS MP ,
COUNT(unsafe_spaces_field_or_playground) AS PG ,
COUNT(unsafe_spaces_dumping_ground) AS DG ,
COUNT(unsafe_spaces_railway_station) AS RS ,
COUNT(unsafe_spaces_route_to_toilet) AS RT ,
COUNT(unsafe_spaces_toilet) AS ST ,
COUNT(unsafe_spaces_well_or_water_pump) AS WT ,
COUNT(unsafe_spaces_river_pond) AS RP ,
COUNT(unsafe_spaces_sewer) AS SS 
FROM formdata

我得到的结果如下:

AH  SCH MP  PG  DG  RS  RT  ST  WT  RP  SS
===========================================
 0  0   12  1   7   16  2   9   0   9   1

但是我需要这样的结果:

  top_3_columns
 ================
   RS,MP,ST

我怎样才能做到这一点?

Answer 1

考虑列名为a、b、c、d和e然后可以使用以下查询

select  tagName, value from 
(select  'a'  tagName, count(a)  value from formdata  UNION
select  'b'  tagName, count(b)  value from formdata  UNION
select   'c'  tagName, count(c)  value from formdata  UNION
select  'd'  tagName, count(d)  value from formdata  UNION
select   'e'  tagName, count(e)  value from formdata  
) 
as results
order by value desc 
limit 3;