mysql 实现数据行转列

我们查询一对多关系,比如一个订单有多个使用人,需要将多个使用人放在一个订单列展示

举例sql,先通过with 建一张虚拟表,虚拟表中给数据按照  ROW_NUMBER() OVER 函数进行排序

with right_order_details_report_xn as 
(select ROW_NUMBER() OVER (PARTITION BY right_order_no ORDER BY right_order_no DESC) AS use_sort,rodr.*  from right_order_details_report rodr)
select t1.*, t2.service_use_name as service_use_name_02,
t2.service_use_sex as service_use_sex_02,
t2.service_use_birth as service_use_birth_02,
t2.service_use_nation as service_use_nation_02,
t2.service_use_certi as service_use_certi_02,
t2.service_use_certiid as service_use_certiid_02,
t2.service_use_tel as service_use_tel_02,
t2.service_use_packageset as service_use_packageset_02,
t2.service_use_servicepack as service_use_servicepack_02,

t3.service_use_name as service_use_name_03,
t3.service_use_sex as service_use_sex_03,
t3.service_use_birth as service_use_birth_03,
t3.service_use_nation as service_use_nation_03,
t3.service_use_certi as service_use_certi_03,
t3.service_use_certiid as service_use_certiid_03,
t3.service_use_tel as service_use_tel_03,
t3.service_use_packageset as service_use_packageset_03,
t3.service_use_servicepack as service_use_servicepack_03

from right_order_details_report_xn t1
 left join right_order_details_report_xn t2 on t1.right_order_no=t2.right_order_no and t2.use_sort=2
 left join right_order_details_report_xn t3 on t1.right_order_no=t3.right_order_no and t3.use_sort=3
 where t1.use_sort=1

这样通过自己关联自己查询，只查询第一行,并且将2，3行关联到第一行中

如图