MySQL行转列

SELECT
    s.testsystem,
sum(case when s.severity ='一般缺陷' then s.cnt else 0 end) as '一般缺陷',
sum(case when s.severity ='致命缺陷' then s.cnt else 0 end)as '致命缺陷',
sum(case when s.severity ='严重缺陷' then s.cnt else 0 end) as '严重缺陷',
sum(case when s.severity ='建议缺陷'  or s.severity="微小缺陷" then s.cnt else 0 end) as '建议缺陷&微小缺陷'
FROM
    (
        SELECT
            t.testsystem,
            t.severity,
            count(1) AS cnt
        FROM
            trpbuginfo t
        GROUP BY
            t.severity,
            t.testsystem
    ) s
GROUP BY
    s.testsystem
    
或者下面复杂的方法

select s.testsystem,a.ac,b.bc,c.cc,d.dc from  
(select testsystem from trpbuginfo group by testsystem)s 
LEFT JOIN (select severity,testsystem,count(*) ac from trpbuginfo where severity="一般缺陷" group by testsystem)a on s.testsystem=a.testsystem
LEFT JOIN (select severity,testsystem,count(*) bc from trpbuginfo where severity="严重缺陷" group by testsystem)b on s.testsystem=b.testsystem
LEFT JOIN (select severity,testsystem,count(*) cc from trpbuginfo where severity="建议缺陷" or severity="微小缺陷" group by testsystem)c on s.testsystem=c.testsystem
LEFT JOIN (select severity,testsystem,count(*) dc from trpbuginfo where severity="致命缺陷" group by testsystem)d on s.testsystem=d.testsystem 
where a.testsystem is NOT NULL 
OR b.testsystem is NOT NULL
OR c.testsystem is NOT NULL
OR d.testsystem is NOT NULL;