mysql - SQL查询问题转MySQL

Question

有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

这与转置、枢轴有关。

Answer 1

最通用的方法是从查询开始，然后使用单独的逻辑进行逆透视:

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