我需要获取表格中计数最高的前三个列的列名。
我正在使用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
我该如何实现这个目标?
COUNT()
提供一个参数,如果该参数评估为非“NULL”值,则计算记录,因此以这种方式在多个列上执行此类操作表明您拥有一个稀疏表(可能是使用实体-属性-值模型重新设计的好候选对象),在其中使用GROUP BY attribute ORDER BY COUNT(*) DESC LIMIT 3
非常容易实现此结果。 - eggyal