mysql> use test;
mysql> create table subject(id int,subname varchar(20));
mysql> insert into subject values(1,'跳绳'),(2,'实心球'),(3,'跳远');
mysql> create table archivement(id int,number int,studentid int,sub_id int,fraction int);
insert into archivement values(1,1001,1,1,50);
insert into archivement values(2,1002,1,1,60);
insert into archivement values(3,1003,1,1,60);
insert into archivement values(4,1004,1,2,10);
insert into archivement values(5,1002,1,2,40);
insert into archivement values(6,1002,1,2,55);
insert into archivement values(7,1002,1,3,99);
insert into archivement values(8,1002,1,3,79);
insert into archivement values(9,1002,1,3,70);
select bb.studentid,max(case bb.subname when '跳绳' then bb.fs end) as'跳绳',
max(case bb.subname when '实心球' then bb.fs end) as '实心球',
max(case bb.subname when '跳远' then bb.fs end) as '跳远'
from (
SELECT
b.studentid,
-- b.sub_id,
a.subname,
max(b.fraction ) fs
FROM
archivement b,
SUBJECT a
WHERE
a.id = b.sub_id
GROUP BY
b.studentid,
b.sub_id
) bb
group by bb.studentid