mysql - SQL查询问题转置MySQL

有2张简单的桌子

People:
person_id
Name

Reading assestment
date
person_id
quality
speed

尝试创建SQL查询

SELECT
  AVG(r.quality),
  AVG(r.speed),
FROM reading_assestment r,people p
where r.person_id =p.person_id
  and person_id="3"

电流输出：

Quality  Speed
77      65

我正在寻找的结果：

Assestment Value
Quality      77
Speed        65

这与转置，枢轴有关。

最佳答案

最一般的方法是从查询开始，然后使用单独的逻辑取消透视：

select (case when n = 1 then 'Quality' else 'Speed' end) as Assessment,
       (case when n = 1 then avg_quality else avg_speed end) as Value
from (select AVG(r.quality) as avg_quality, AVG(r.speed) as avg_speed
      from reading_assestment r join
           people p
           on r.person_id =p.person_id
      where person_id = 3
     ) t cross join
     (select 1 as n union all select 2) n

关于mysql - SQL查询问题转置MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20998750/