To simplify a list after league tables (personnel)
Table structure is as follows: id unitId subjectId score
1 a 1001, 80,
2 b, 1002, 90
3 b, 1001, 90
4 c 1003 70
.

Want to conclude after the query record
SubjectId a b c d e...
1001 the sum (score) sum (score) sum (score) sum (score) sum (score)
1002 the sum (score) sum (score) sum (score) sum (score) sum (score)
1003 the sum (score) sum (score) sum (score) sum (score) sum (score)
1004 the sum (score) sum (score) sum (score) sum (score) sum (score)

CodePudding user response:

Try

 select group_concat (' sum (if (unitId=' ' ', unitId, ' ' ', score, 0)) as "', unitId, '"') into @ SQL FROM (select DISTINCT unitId personnel record table) as t. 
Set @ SQL=concat (' the select subjectId, '@ SQL,' the from personnel record table group by subjectId "); 
Prepare STMT from @ SQL; 
The execute STMT. 
Deallocate prepare STMT. 

CodePudding user response:

reference 1st floor roy_88 response:

try

 select group_concat (' sum (if (unitId=' ' ', unitId, ' ' ', score, 0)) as "', unitId, '"') into @ SQL FROM (select DISTINCT unitId personnel record table) as t. 
Set @ SQL=concat (' the select subjectId, '@ SQL,' the from personnel record table group by subjectId "); 
Prepare STMT from @ SQL; 
The execute STMT. 
Deallocate prepare STMT. 

If it's not stored procedures, write

CodePudding user response:

This is a dynamic writing
Your unitId is dynamic, not a fixed value

A fixed value can

 select subjectId 
, the SUM (the IF (unitId='a' score, 0)) AS a 
='b', the SUM (the IF (unitId, score, 0)) AS b 
# to the above - other 
The FROM personnel record table 
Group by subjectId