There is a the curriculum subject table

There is also a student achievement table achievement id in the subject in the student table sub_id corresponding curriculum

Now in the course of subName the solid ball jump rope skipping show into three columns and take student achievement all subjects with the highest grades in table achievement how show

CodePudding user response:

the result for student achievement in the fraction is score

CodePudding user response:

Line column operation,

CodePudding user response:

The solid ball jump rope skipping display into three columns? The three set column names, and then the corresponding maximum score is the column value is it?

CodePudding user response:

 SELECT 
S.s ubName subjects, 
(SELECT MAX (fraction) of the FROM achievement WHERE sub_id=s.i d) highest score 
The FROM 
The SUBJECT AS s; 

CodePudding user response:

Mysql> Use the test;
Mysql> The create table subject (id int, subname varchar (20));
Mysql> Insert into subject values (1, 'rope'), (2, 'medicine balls), (3,' jump ');
Mysql> The create table archivement (id int, number int, studentid int, sub_id int, fraction int);
Insert into archivement values (1100 1,1,1,50);
Insert into archivement values 2,1,1,60 (2100);
3,1,1,60 insert into archivement values (3100);
Insert into archivement values 4,1,2,10 (4100);
Insert into archivement values 2,1,2,40 (5100);
Insert into archivement values 2,1,2,55 (6100);
Insert into archivement values 2,1,3,99 (7100);
Insert into archivement values 2,1,3,79 (8100);
Insert into archivement values 2,1,3,70 (9100);


Select bb studentid, Max (case bb. Subname when 'rope' then bb. Fs end) as' jumping rope,
Max (case bb. Subname when 'solid sphere then bb. Fs end) as' medicine balls,
Max (case bb. Subname when 'jump' then bb. Fs end) as' jump '
The from (
SELECT
B.s tudentid,
- b.s ub_id,
A.s ubname,
Max (b. raction) fs
The FROM
Archivement b,
The SUBJECT of a
WHERE
Anderson, d=b.s ub_id
GROUP BY
B.s tudentid,
B.s ub_id
Bb)
Group by bb. Studentid

CodePudding user response:

Before written in dry set calculate embellish SPL statements made a career change, you can have a try, use is quite simple,
Before the transformation:
After the transformation:
Grid written statement looks something like this:
A1:=file (" E:/test. XLSX "). The xlsimport @ t ()
A2:=A1. Pivot (CLASS, STUDENTID; SUBJECT to SCORE." English ", "English", "Math", "PE")

CodePudding user response:

You should use a program step by step to realize the demand