php - 使用 php mysql 查询选择具有最高值的 5 列

Question

我需要 5 列具有最高值的报告(值必须是每列的总和)。我有大约 40 个 contury 列，例如 england_turist、polad_turists、us_turists、india_turists... eng - 152(sum)、pol - 280(sum)、us - 54(sum)、in - 36(sum)... 和查询需要选择 pol - 280 和 eng - 152。因此查询需要对每一列求和并选择具有最高值的 5 列。

我从这里得到了结果，但如何创建查询

SELECT * FROM
(
     SELECT 'Bmw' AS vrstaautomobila, SUM(bmw) AS brautomobila FROM unos_korisnika
     UNION
     SELECT 'Mercedes' AS vrstaautomobila, SUM(mercedes) AS brautomobila FROM unos_korisnika
     UNION
     SELECT 'Opel' AS vrstaautomobila, SUM(opel) AS brautomobila FROM unos_korisnika
     UNION
     SELECT 'Nissan' AS vrstaautomobila, SUM(nissan) AS brautomobila FROM unos_korisnika
     UNION
     SELECT 'Peguoet' AS vrstaautomobila, SUM(peguoet) AS brautomobila FROM unos_korisnika
     UNION
     SELECT 'Volkswagen' AS vrstaautomobila, SUM(volkswagen) AS brautomobila FROM unos_korisnika
)
AS results
ORDER BY brautomobila DESC
LIMIT 5

Answer 1

转置表格(将列转换为行)，然后选择顶部值。

select * from transposed_table order by value_column desc limit 5

您可以使用How to transpose mysql table rows into columns转置您的数据或者可能是这个 SQL - How to transpose?