?? 行轉列.txt
字號:
/*********橫向查詢oracle和sql通用**********/
drop table students;
drop table score;
create table students
( stu_code char(3) constraint pk_students primary key,
stu_name varchar(20),
stu_sex char(1)
);
create table score
( stu_code char(3) constraint fk_score_students references students(stu_code),
sub_name varchar(10),
stu_score numeric(4,1),
constraint pk_score primary key (stu_code,sub_name)
);
insert into students values('001','張三',0);
insert into students values('002','李四',1);
insert into students values('003','王五',0);
insert into students values('004','張可阿',1);
insert into students values('005','王三分',1);
insert into students values('006','李渴望',1);
insert into score values('001','語文',85);
insert into score values('002','語文',76);
insert into score values('003','語文',32);
insert into score values('004','語文',78);
insert into score values('005','語文',45);
insert into score values('006','語文',34);
insert into score values('001','數學',55);
insert into score values('002','數學',66);
insert into score values('003','數學',79);
insert into score values('004','數學',45);
insert into score values('005','數學',41);
insert into score values('006','數學',29);
insert into score values('001','英語',56);
insert into score values('002','英語',54);
insert into score values('003','英語',78);
insert into score values('004','英語',95);
insert into score values('005','英語',25);
insert into score values('006','英語',34);
select Stu_Code,sum(Chinese) 語文,sum(Math) 數學,sum(English) 英語
from
(Select stu_code,stu_score Chinese,0 Math,0 English From score Where sub_name='語文'
union
Select stu_code,0 Chinese,stu_score Math,0 English From score Where sub_name='數學'
union
Select stu_code,0 Chinese,0 Math,stu_score English From score Where sub_name='英語') A
Group By Stu_Code
/*********效果顯示***************/
STU 語文 數學 英語
--- ---------- ---------- ----------
001 85 55 56
002 76 66 54
003 32 79 78
004 78 45 95
005 45 41 25
006 34 29 34
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -