行查列显

create table cw_user
(
       user_id VARCHAR2(5),
       user_name VARCHAR2(20),
       user_sex VARCHAR2(2)
);

CREATE TABLE CW_SCORE
(
       score_id VARCHAR2(5),
       score_user_id VARCHAR2(5),
       score_subject VARCHAR2(20),
       score_point float
);

alter table CW_user 
      add constraint PK_CW_USER primary key(user_id);
     
alter table CW_SCORE
      add constraint PK_CW_SCORE primary key(score_id);
      
alter table CW_SCORE
      add constraint FK_CW_SCORE foreign key(score_user_id)
      references CW_user(user_id);     

insert into CW_USER(user_id, USER_NAME, USER_SEX)
       values('1','john','x');
       
insert into CW_USER(user_id, USER_NAME, USER_SEX)
       values('2','jeck','y');
       
insert into CW_USER(user_id, USER_NAME, USER_SEX)
       values('3','kingkong','x');
       
INSERT INTO CW_SCORE(score_id,Score_User_Id,SCORE_SUBJECT,SCORE_POINT)
       VALUES('1','1','语文',99.5);
INSERT INTO CW_SCORE(score_id,Score_User_Id,SCORE_SUBJECT,SCORE_POINT)
       VALUES('2','1','数学',98.5);
INSERT INTO CW_SCORE(score_id,Score_User_Id,SCORE_SUBJECT,SCORE_POINT)
       VALUES('3','1','英语',99);
INSERT INTO CW_SCORE(score_id,Score_User_Id,SCORE_SUBJECT,SCORE_POINT)
       VALUES('4','1','历史',89);
INSERT INTO CW_SCORE(score_id,Score_User_Id,SCORE_SUBJECT,SCORE_POINT)
       VALUES('5','1','地理',93);

INSERT INTO CW_SCORE(score_id,Score_User_Id,SCORE_SUBJECT,SCORE_POINT)
       VALUES('6','2','语文',94.5);
INSERT INTO CW_SCORE(score_id,Score_User_Id,SCORE_SUBJECT,SCORE_POINT)
       VALUES('7','2','数学',95);
INSERT INTO CW_SCORE(score_id,Score_User_Id,SCORE_SUBJECT,SCORE_POINT)
       VALUES('8','2','英语',97);
INSERT INTO CW_SCORE(score_id,Score_User_Id,SCORE_SUBJECT,SCORE_POINT)
       VALUES('9','2','历史',99);
INSERT INTO CW_SCORE(score_id,Score_User_Id,SCORE_SUBJECT,SCORE_POINT)
       VALUES('10','2','地理',73);

SELECT * FROM CW_SCORE;
SELECT * FROM CW_USER;

select u.user_id 用户id,u.user_name 姓名, 
       max(case t.score_subject when '语文' then t.score_point else 0 end) 语文,
       max(case t.score_subject when '数学' then t.score_point else 0 end) 数学,
       max(case t.score_subject when '英语' then t.score_point else 0 end) 英语,
       max(case t.score_subject when '历史' then t.score_point else 0 end) 历史,
       max(case t.score_subject when '地理' then t.score_point else 0 end) 地理,
       max(case u.user_sex when 'y' then '女' else '男' end) 性别
from cw_score t,cw_user u group by u.user_name,t.score_user_id,u.user_id having t.score_user_id = u.user_id order by u.user_id;

 

--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名

--