PHP中mysql中同一行中的前5个数字

Question

How to get the first highest numbers in the same row in mysql for example i have this row

I have this database

Name Level1 Level2 Level3 Level4 Level5 Level6 Level7 Level8 Level9 Level10 Level11 Level12
armar 80 50 30 60 80 60 90 35 90 66 55 22

ok i want to get the top 5 levels in that row in mysql and order from higher to lower

example result will be : 90 90 80 80 60

Thanks!

Answer 1

A PHP solution would be better here, but if you just need a MySQL query you could use this:

SELECT
  Name,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Levels, ',', 1), ',', -1) Top1,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Levels, ',', 2), ',', -1) Top2,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Levels, ',', 3), ',', -1) Top3,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Levels, ',', 4), ',', -1) Top4,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Levels, ',', 5), ',', -1) Top5
FROM (
  SELECT Name, GROUP_CONCAT(Level ORDER BY Level DESC) Levels
  FROM (
    SELECT Name, Level1 Level FROM yourtable
    UNION ALL
    SELECT Name, Level2 FROM yourtable
    UNION ALL
    SELECT Name, Level3 FROM yourtable
    UNION ALL
    SELECT Name, Level4 FROM yourtable
    UNION ALL
    SELECT Name, Level5 FROM yourtable
    UNION ALL
    SELECT Name, Level6 FROM yourtable
    UNION ALL
    SELECT Name, Level7 FROM yourtable
    UNION ALL
    SELECT Name, Level8 FROM yourtable
    UNION ALL
    SELECT Name, Level9 FROM yourtable
    UNION ALL
    SELECT Name, Level10 FROM yourtable
    UNION ALL
    SELECT Name, Level11 FROM yourtable
    UNION ALL
    SELECT Name, Level12 FROM yourtable
  ) s
  GROUP BY Name
) s

Please see fiddle here.