一、使用sum(case when)
废话不多说,直接上图
select projectDeclare_id,
sum(case when seq=0 then score1 else NULL end) as 'diyihang',
sum(case when seq=1 then score1 else NULL end) as 'diyihang',
sum(case when seq=2 then score1 else NULL end) as 'diyihang',
sum(case when seq=3 then score1 else NULL end) as 'diyihang',
sum(case when seq=4 then score1 else NULL end) as 'diyihang',
sum(case when seq=5 then score1 else NULL end) as 'diyihang',
sum(case when seq=6 then score1 else NULL end) as 'diyihang',
sum(case when seq=7 then score1 else NULL end) as 'diyihang',
sum(case when seq=8 then score1 else NULL end) as 'diyihang',
sum(case when seq=9 then score1 else NULL end) as 'diyihang',
sum(case when seq=10 then score1 else NULL end) as 'diyihang',
sum(case when seq=11 then score1 else NULL end) as 'diyihang',
sum(case when seq=12 then score1 else NULL end) as 'diyihang',
sum(case when seq=13 then score1 else NULL end) as 'dierhang'
from (
SELECT
*
FROM
Lab_Performance lp
WHERE
lp.projectDeclare_id IN (
SELECT
pd.id
FROM
Pro_Declare pd
LEFT JOIN pro_main pm ON pm.id = pd.projectMain_id
LEFT JOIN Pro_Plan pp ON pp.id = pm.projectPlan_id
WHERE
pp.projectname LIKE '%孵化器统计绩效%'
AND pd.uniCode LIKE '%绩效_2022_二季度%'
)
) as b group by projectDeclare_id
需要用到行列转换的表一般都会用到三个字段
1、表中表示同一组数据的字段，我这里就是declare_id
2、需要分列的字段数据，就是转换成行之后的字段，我这里用的是seq
3、不同seq对应的数据，这个可以有很多个，多用几个case when 就行
sum(case when seq='?' then score else NULL end) as '字段名'
by the way，这里面select ..from(select..) as b这里需要有一个as 可能是子查询需要命名吧
