sql 学生成绩表

create table student
(
  id   VARCHAR2(10) not null,
  name VARCHAR2(20) not null
)

create table prices
(
  sid   VARCHAR2(10) not null,
  cid   VARCHAR2(20) not null,
  price VARCHAR2(20) not null
)

create table class
(
  id   VARCHAR2(10) not null,
  name VARCHAR2(20) not null
)






SELECT p.sid as 学生ID
        ,(select s.name from student s where s.id=p.sid ) as 名称
        ,(SELECT p1.price FROM prices p1 WHERE p1.sid=p.sid and p1.cid=1) AS 语文
        ,(SELECT p2.price FROM prices p2 WHERE p2.sid=p.sid and p2.cid=3) AS 数学
    FROM prices AS p
    GROUP BY p.sid
    ORDER BY p.sid desc;



SELECT p2.price ,p2.sid '学号',s.`name` '名称',c.`name`
FROM score p2,student s,class c WHERE c.id=p2.cid and s.id=p2.sid
ORDER BY s.name;