表中记录变字段:
表 a_a :
student_id | student_name | subject | grade |
20070001 | mark | math | 88 |
20070001 | mark | chinese | 90 |
20070001 | mark | english | 95 |
20070002 | green | math | 96 |
20070002 | green | chinese | 92 |
20070002 | green | english | 91 |
如何由上表a_a 得到下表a
student_id | student_name | math | chinese | english |
20070001 | mark | 88 | 90 | 95 |
20070002 | green | 96 | 92 | 91 |
实现的sql语句:
select a.student_id,a.student_name,
(select grade from a_a where student_id=a.student_id and student_name=a.student_name and subject='math') as math,
(select grade from a_a where student_id=a.student_id and student_name=a.student_name and subject='chinese') as chinese,
(select grade from a_a where student_id=a.student_id and student_name=a.student_name and subject='english') as english
from (SELECT student_id,student_name FROM a_agroup by student_id,student_name) a