如何将多列输出作为一列并找到该列的顶部 - How to take multiple column output as one column and find top of that column

How to take multiple column output as one column and find top of that column

本文关键字：一列顶部输出 | 更新日期: 2023-09-26

我被困在一个场景中，我选择表中每列的平均值。现在我想找出前三列。

这是我的查询:

SELECT AVG( `adventure` ) , AVG( `beach` ) , AVG( `culture` ) , AVG( `festival` ) , AVG( `food` ) , AVG( `fun` ) , AVG( `biking` ) , AVG( `ski` ) , AVG( `surf` ) , AVG( `family` ) , AVG( `couple` ) , AVG( `single` ) , AVG( `backpacker` )
FROM `ratings`
WHERE `reference_id`=4595

这个查询返回每列的平均值。但是我只想选择前3个平均值。

提前感谢!

我猜你需要先标准化你的表但这里有一个查询符合你的需求，

顺便说一下，它返回两列，计算平均值的列名和平均值的结果。

SELECT  Category, avgVal
FROM
    (
        SELECT  'adventure' AS Category, AVG(`adventure`) AS avgVal FROM `ratings` WHERE `reference_id`=4595
        UNION
        SELECT  'beach' AS Category, AVG(`beach`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595
        UNION
        SELECT  'culture' AS Category, AVG(`culture`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'festival' AS Category, AVG(`festival`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'food' AS Category, AVG(`food`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595
        UNION
        SELECT  'fun' AS Category, AVG(`fun`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'biking' AS Category, AVG(`biking`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'ski' AS Category, AVG(`ski`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'surf' AS Category, AVG(`surf`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'family' AS Category, AVG(`family`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'couple' AS Category, AVG(`couple`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'single' AS Category, AVG(`single`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'backpacker' AS Category, AVG(`backpacker`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
    ) s
ORDER BY avgVal DESC
LIMIT 3

您需要在这里使用子查询

 SELECT AVG( `adventure` ) 
       ,AVG( `beach` )
       ,AVG( `culture` )
       , AVG( `festival` )
       , AVG( `food` )
       , AVG( `fun` )
       , AVG( `biking` )
       , AVG( `ski` )
       , AVG( `surf` )
       , AVG( `family` )
       , AVG( `couple` )
       , AVG( `single` )
       , AVG( `backpacker` ) 
FROM ( SELECT adventure,beach,culture,festival,food,fun,biking,ski,surf,family,couple,single,backpacker
       FROM `ratings`
       WHERE `reference_id`=4595 
       LIMIT 0,3
) DerivedTable

希望这对你有帮助。由于