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;