SELECT s.subName 科目, ( SELECT MAX( fraction ) FROM achievement WHERE sub_id = s.id ) 最高成绩 FROM SUBJECT AS s;
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
s.subName 科目,
( SELECT MAX( fraction ) FROM achievement WHERE sub_id = s.id ) 最高成绩
FROM
SUBJECT AS s;
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
转换前:
转换后:
网格语句大概是这样写的:
A1:=file("E:/test.xlsx").xlsimport@t()
A2:=A1.pivot(CLASS,STUDENTID;SUBJECT,SCORE;"English":"ENGLISH","Math","PE")