有如下的一张表(3个字段)
NAME cur_cd SCORE
jim math 60
tom eng 30
lucy jap 45
jim eng 90
tom jap 80
sam tech 77
我想要得到每个人的所有科目的分数,查询结果要以以下格式输出:
Name math eng jap tech
jim 60 90
tom 30 80
lucy 45
sam 77请教各位哥哥姐姐这个sql应该怎么写,
感激不尽!
NAME cur_cd SCORE
jim math 60
tom eng 30
lucy jap 45
jim eng 90
tom jap 80
sam tech 77
我想要得到每个人的所有科目的分数,查询结果要以以下格式输出:
Name math eng jap tech
jim 60 90
tom 30 80
lucy 45
sam 77请教各位哥哥姐姐这个sql应该怎么写,
感激不尽!
解决方案 »
- 怎么取前十条数据!
- 请教一个ORACLE删除时判断外键是否有数据的问题
- 两表关联查询问题,sql应该怎么写
- 跪问!!!Oracle9i无法增加控制文件!!!!
- 如何建立定长序列,如001~999,不足三位的用'0'补齐?
- 呵呵概念问题:oracle的包装上写着:50用户。这里的50用户是不是连接数为50之后就不能再有新的连接进来的拉?
- 怎么样Select 到一个数据列表,然后在下面的Select 语句中使用In (上次检索到的记录列表)
- sql plus执行脚本出错,请高手帮忙,急!
- 有没有关于sql*plus命令的网站?(在线守侯)
- sql 遍历提取值相同的 并保存到新字段中
- 求教关于行转列的问题
- excel数据导入oracle--System.IO.IOException: 文件''正由另一进程使用,因此该进程无法访问该文件。
SQL>
SQL> with A as (
2 select 'jim' name , 'math' cur_cd, 60 score from dual union all
3 select 'tom' name , 'eng' cur_cd, 30 score from dual union all
4 select 'lucy' name , 'jap' cur_cd, 45 score from dual union all
5 select 'jim' name , 'eng' cur_cd, 90 score from dual union all
6 select 'tom' name , 'jap' cur_cd, 80 score from dual union all
7 select 'sam' name , 'tech' cur_cd, 77 score from dual)
8 select name, max(decode(cur_cd, 'math',score , 0)) math,
9 max(decode(cur_cd, 'eng',score , 0)) eng,
10 max(decode(cur_cd, 'jap',score , 0)) jap,
11 max(decode(cur_cd, 'tech',score , 0)) tech
12 from A group by name
13 /NAME MATH ENG JAP TECH
---- ---------- ---------- ---------- ----------
jim 60 90 0 0
lucy 0 0 45 0
sam 0 0 0 77
tom 0 30 80 0SQL>
但是源表的数据不是固定的(Name 人和cur_cd 科目)
但是总共的科目不会超过40个
好像是 select name,sum(case cur_cd when 'math' then CORE end )as math,
sum(case cur_cd when 'eng' then CORE end )as eng,
sum(case cur_cd when 'jap' then CORE end )as jap,
sum(case cur_cd when 'tech ' then CORE end )as tech
from 表 group by name;
看看這術能不能行咯!
--以生成视图方式来做
--g_col 分组列
--v_col 列转行对应值列scott@ORCL> create or replace procedure proc_stu_class(g_col varchar2,v_col varchar2)
2 as
3 str varchar2(4000);
4 begin
5 for i in(select distinct cur_cd from stu_class) loop
6 str:=str||',max(decode(cur_cd,'''||i.cur_cd||''','||v_col||')) as '||i.cur_cd;
7 end loop;
8 str:='select '||g_col||str||' from stu_class group by '||g_col;
9 execute immediate 'create or replace view v_stu_class as '||str;
10 dbms_output.put_line(str);
11 end;
12 /过程已创建。scott@ORCL> exec proc_stu_class('name','score')PL/SQL 过程已成功完成。
---视图结果
scott@ORCL> select * from v_stu_class
2 /NAME ENG MATH TECH JAP
---- ---------- ---------- ---------- ----------
tom 30 80
sam 77
jim 90 60
lucy 45scott@ORCL> select * from stu_class
2 /NAME CUR_ SCORE
---- ---- ----------
jim math 60
tom eng 30
lucy jap 45
jim eng 90
tom jap 80
sam tech 77已选择6行。
name "Name",
MAX(DECODE(cur_cd,'math',score,NULL)) "math",
MAX(DECODE(cur_cd,'eng',score,NULL)) "eng",
MAX(DECODE(cur_cd,'jap',score,NULL)) "jap",
MAX(DECODE(cur_cd,'tech',score,NULL)) "tech"
FROM t
GROUP BY name
IS
v_str varchar2(2000) :='select name';
BEGIN
FOR i IN(SELECT DISTINCT cur_cd FROM tmp) loop
v_str :=v_str||',max(decode(cur_cd ,'''||i.cur_cd||''',score)) as '||i.cur_cd;
END loop;
v_str := v_str ||' from tmp group by name ';
OPEN cur FOR v_str;
END;
/var cur refcursor
exec sp_t(:cur)
print cur
以下是我的结果:
SELECT '',
TO_CHAR(MAX(DECODE(ROWNUM, '1', B.cur_cd, ' '))) cur_cd1,
TO_CHAR(MAX(DECODE(ROWNUM, '2', B.cur_cd, ' '))) cur_cd2,
TO_CHAR(MAX(DECODE(ROWNUM, '3', B.cur_cd, ' '))) cur_cd3,
TO_CHAR(MAX(DECODE(ROWNUM, '4', B.cur_cd, ' '))) cur_cd4,
TO_CHAR(MAX(DECODE(ROWNUM, '5', B.cur_cd, ' '))) cur_cd5,
TO_CHAR(MAX(DECODE(ROWNUM, '6', B.cur_cd, ' '))) cur_cd6,
TO_CHAR(MAX(DECODE(ROWNUM, '7', B.cur_cd, ' '))) cur_cd7,
TO_CHAR(MAX(DECODE(ROWNUM, '8', B.cur_cd, ' '))) cur_cd8,
TO_CHAR(MAX(DECODE(ROWNUM, '9', B.cur_cd, ' '))) cur_cd9,
TO_CHAR(MAX(DECODE(ROWNUM, '10', B.cur_cd, ' '))) cur_cd10,
TO_CHAR(MAX(DECODE(ROWNUM, '11', B.cur_cd, ' '))) cur_cd11,
TO_CHAR(MAX(DECODE(ROWNUM, '12', B.cur_cd, ' '))) cur_cd12,
TO_CHAR(MAX(DECODE(ROWNUM, '13', B.cur_cd, ' '))) cur_cd13,
TO_CHAR(MAX(DECODE(ROWNUM, '14', B.cur_cd, ' '))) cur_cd14,
TO_CHAR(MAX(DECODE(ROWNUM, '15', B.cur_cd, ' '))) cur_cd15,
TO_CHAR(MAX(DECODE(ROWNUM, '16', B.cur_cd, ' '))) cur_cd16,
TO_CHAR(MAX(DECODE(ROWNUM, '17', B.cur_cd, ' '))) cur_cd17,
TO_CHAR(MAX(DECODE(ROWNUM, '18', B.cur_cd, ' '))) cur_cd18,
TO_CHAR(MAX(DECODE(ROWNUM, '19', B.cur_cd, ' '))) cur_cd19,
TO_CHAR(MAX(DECODE(ROWNUM, '20', B.cur_cd, ' '))) cur_cd20,
TO_CHAR(MAX(DECODE(ROWNUM, '21', B.cur_cd, ' '))) cur_cd21,
TO_CHAR(MAX(DECODE(ROWNUM, '22', B.cur_cd, ' '))) cur_cd22,
TO_CHAR(MAX(DECODE(ROWNUM, '23', B.cur_cd, ' '))) cur_cd23,
TO_CHAR(MAX(DECODE(ROWNUM, '24', B.cur_cd, ' '))) cur_cd24,
TO_CHAR(MAX(DECODE(ROWNUM, '25', B.cur_cd, ' '))) cur_cd25,
TO_CHAR(MAX(DECODE(ROWNUM, '26', B.cur_cd, ' '))) cur_cd26,
TO_CHAR(MAX(DECODE(ROWNUM, '27', B.cur_cd, ' '))) cur_cd27,
TO_CHAR(MAX(DECODE(ROWNUM, '28', B.cur_cd, ' '))) cur_cd28,
TO_CHAR(MAX(DECODE(ROWNUM, '29', B.cur_cd, ' '))) cur_cd29,
TO_CHAR(MAX(DECODE(ROWNUM, '30', B.cur_cd, ' '))) cur_cd30,
TO_CHAR(MAX(DECODE(ROWNUM, '31', B.cur_cd, ' '))) cur_cd31,
TO_CHAR(MAX(DECODE(ROWNUM, '32', B.cur_cd, ' '))) cur_cd32,
TO_CHAR(MAX(DECODE(ROWNUM, '33', B.cur_cd, ' '))) cur_cd33,
TO_CHAR(MAX(DECODE(ROWNUM, '34', B.cur_cd, ' '))) cur_cd34,
TO_CHAR(MAX(DECODE(ROWNUM, '35', B.cur_cd, ' '))) cur_cd35,
TO_CHAR(MAX(DECODE(ROWNUM, '36', B.cur_cd, ' '))) cur_cd36,
TO_CHAR(MAX(DECODE(ROWNUM, '37', B.cur_cd, ' '))) cur_cd37,
TO_CHAR(MAX(DECODE(ROWNUM, '38', B.cur_cd, ' '))) cur_cd38,
TO_CHAR(MAX(DECODE(ROWNUM, '39', B.cur_cd, ' '))) cur_cd39,
TO_CHAR(MAX(DECODE(ROWNUM, '40', B.cur_cd, ' '))) cur_cd40,
TO_CHAR(MAX(DECODE(ROWNUM, '41', B.cur_cd, ' '))) cur_cd41,
TO_CHAR(MAX(DECODE(ROWNUM, '42', B.cur_cd, ' '))) cur_cd42,
TO_CHAR(MAX(DECODE(ROWNUM, '43', B.cur_cd, ' '))) cur_cd43,
TO_CHAR(MAX(DECODE(ROWNUM, '44', B.cur_cd, ' '))) cur_cd44,
TO_CHAR(MAX(DECODE(ROWNUM, '45', B.cur_cd, ' '))) cur_cd45,
TO_CHAR(MAX(DECODE(ROWNUM, '46', B.cur_cd, ' '))) cur_cd46,
TO_CHAR(MAX(DECODE(ROWNUM, '47', B.cur_cd, ' '))) cur_cd47,
TO_CHAR(MAX(DECODE(ROWNUM, '48', B.cur_cd, ' '))) cur_cd48,
TO_CHAR(MAX(DECODE(ROWNUM, '49', B.cur_cd, ' '))) cur_cd49,
TO_CHAR(MAX(DECODE(ROWNUM, '50', B.cur_cd, ' '))) cur_cd50
FROM (select ROWNUM, cur_cd
FROM (SELECT DISTINCT cur_cd FROM TABLE1 ORDER BY cur_cd ) A) B
UNION ALL
SELECT D.cur_cd ,
TO_CHAR(SUM(DECODE(NM, '1', D.SCORE, '0'))) cur_cd1,
TO_CHAR(SUM(DECODE(NM, '2', D.SCORE, '0'))) cur_cd2,
TO_CHAR(SUM(DECODE(NM, '3', D.SCORE, '0'))) cur_cd3,
TO_CHAR(SUM(DECODE(NM, '4', D.SCORE, '0'))) cur_cd4,
TO_CHAR(SUM(DECODE(NM, '5', D.SCORE, '0'))) cur_cd5,
TO_CHAR(SUM(DECODE(NM, '6', D.SCORE, '0'))) cur_cd6,
TO_CHAR(SUM(DECODE(NM, '7', D.SCORE, '0'))) cur_cd7,
TO_CHAR(SUM(DECODE(NM, '8', D.SCORE, '0'))) cur_cd8,
TO_CHAR(SUM(DECODE(NM, '9', D.SCORE, '0'))) cur_cd9,
TO_CHAR(SUM(DECODE(NM, '10', D.SCORE, '0'))) cur_cd10,
TO_CHAR(SUM(DECODE(NM, '11', D.SCORE, '0'))) cur_cd11,
TO_CHAR(SUM(DECODE(NM, '12', D.SCORE, '0'))) cur_cd12,
TO_CHAR(SUM(DECODE(NM, '13', D.SCORE, '0'))) cur_cd13,
TO_CHAR(SUM(DECODE(NM, '14', D.SCORE, '0'))) cur_cd14,
TO_CHAR(SUM(DECODE(NM, '15', D.SCORE, '0'))) cur_cd15,
TO_CHAR(SUM(DECODE(NM, '16', D.SCORE, '0'))) cur_cd16,
TO_CHAR(SUM(DECODE(NM, '17', D.SCORE, '0'))) cur_cd17,
TO_CHAR(SUM(DECODE(NM, '18', D.SCORE, '0'))) cur_cd18,
TO_CHAR(SUM(DECODE(NM, '19', D.SCORE, '0'))) cur_cd19,
TO_CHAR(SUM(DECODE(NM, '20', D.SCORE, '0'))) cur_cd20,
TO_CHAR(SUM(DECODE(NM, '21', D.SCORE, '0'))) cur_cd21,
TO_CHAR(SUM(DECODE(NM, '22', D.SCORE, '0'))) cur_cd22,
TO_CHAR(SUM(DECODE(NM, '23', D.SCORE, '0'))) cur_cd23,
TO_CHAR(SUM(DECODE(NM, '24', D.SCORE, '0'))) cur_cd24,
TO_CHAR(SUM(DECODE(NM, '25', D.SCORE, '0'))) cur_cd25,
TO_CHAR(SUM(DECODE(NM, '26', D.SCORE, '0'))) cur_cd26,
TO_CHAR(SUM(DECODE(NM, '27', D.SCORE, '0'))) cur_cd27,
TO_CHAR(SUM(DECODE(NM, '28', D.SCORE, '0'))) cur_cd28,
TO_CHAR(SUM(DECODE(NM, '29', D.SCORE, '0'))) cur_cd29,
TO_CHAR(SUM(DECODE(NM, '30', D.SCORE, '0'))) cur_cd30,
TO_CHAR(SUM(DECODE(NM, '31', D.SCORE, '0'))) cur_cd31,
TO_CHAR(SUM(DECODE(NM, '32', D.SCORE, '0'))) cur_cd32,
TO_CHAR(SUM(DECODE(NM, '33', D.SCORE, '0'))) cur_cd33,
TO_CHAR(SUM(DECODE(NM, '34', D.SCORE, '0'))) cur_cd34,
TO_CHAR(SUM(DECODE(NM, '35', D.SCORE, '0'))) cur_cd35,
TO_CHAR(SUM(DECODE(NM, '36', D.SCORE, '0'))) cur_cd36,
TO_CHAR(SUM(DECODE(NM, '37', D.SCORE, '0'))) cur_cd37,
TO_CHAR(SUM(DECODE(NM, '38', D.SCORE, '0'))) cur_cd38,
TO_CHAR(SUM(DECODE(NM, '39', D.SCORE, '0'))) cur_cd39,
TO_CHAR(SUM(DECODE(NM, '40', D.SCORE, '0'))) cur_cd40,
TO_CHAR(SUM(DECODE(NM, '41', D.SCORE, '0'))) cur_cd41,
TO_CHAR(SUM(DECODE(NM, '42', D.SCORE, '0'))) cur_cd42,
TO_CHAR(SUM(DECODE(NM, '43', D.SCORE, '0'))) cur_cd43,
TO_CHAR(SUM(DECODE(NM, '44', D.SCORE, '0'))) cur_cd44,
TO_CHAR(SUM(DECODE(NM, '45', D.SCORE, '0'))) cur_cd45,
TO_CHAR(SUM(DECODE(NM, '46', D.SCORE, '0'))) cur_cd46,
TO_CHAR(SUM(DECODE(NM, '47', D.SCORE, '0'))) cur_cd47,
TO_CHAR(SUM(DECODE(NM, '48', D.SCORE, '0'))) cur_cd48,
TO_CHAR(SUM(DECODE(NM, '49', D.SCORE, '0'))) cur_cd49,
TO_CHAR(SUM(DECODE(NM, '50', D.SCORE, '0'))) cur_cd50
FROM (SELECT ROWNUM NM, C.SCORE, C.cur_cd
FROM (SELECT DISTINCT cur_cd FROM TABLE1 ORDER BY cur_cd) A) B
ORDER BY NM) D
GROUP BY D.SCORE