面试 超难的sql语句

Question

有一个score表结构为:
student_id int,      ---学号
grade varchar2(10);  ---年级
subjects varchar2(20); ----科目
score int ,           ----分数
题目如下:
写出sql语句 
1. score为90分以上的 优秀
   score为70-80      良好
   score为70以下的   不合格2. 写出sql使执行结果如下
 学号   年级   数学  英语  语文  .....(科目不定,表里有多少显示多少)
 0001    1     90     89    70
 0002    1     93     78    70
 0003    1     90     83    80

Answer 1

第一题、
SELECT S.STUDENT_ID 学号,
       S.GRADE 年级,
       S.SUBJECTS 科目,
       S.SCORE 分数,
       CASE
         WHEN S.SCORE >= 90 THEN
          '优秀'
         WHEN S.S.SCORE >= 70 AND S.S.SCORE < 90 THEN
          '良好'
         ELSE
          '不合格'
       END 等级分类
  FROM SCORE S

Answer 2

第一题这样能求解出来，第二题就是行列转换，求有几个科目，select distinct 科目 from 表名;select student_id 学号,grade 年级,sum(math) 数学,sum(english) 英语,sum(chinese) 语文 from (
select student_id,
       grade,
       decode(subjects,'数学',score,0) math,
       decode(subjects,'英语',score,0) english,
       decode(subjects,'语文',score,0) chinese
       from score
)
group by student_id,grade;

Answer 3

面试应该是用2楼的方法按照楼主说的科目不定的话，单独的sql不可能
只能用存储过程动态构造sql

Answer 4

这个帖子已经做出来了：http://topic.csdn.net/u/20090627/10/f1d34c6d-c2fa-4db0-8ace-9253b1484324.html

Answer 5

第二题：
1、
select count(1) from SCORE; (求出科目数量)
2、
SELECT T.STUDENT_ID,
                T.GRADE,
                SUM(CASE
                      WHEN T.SUBJECTS =
                           (SELECT DISTINCT B.SUBJECTS
                              FROM (SELECT SUBJECTS, ROWNUM A FROM SCORE) B
                             WHERE B.A = 1) THEN
                       T.SCORE
                      ELSE
                       0
                    END),
                SUM(CASE
                      WHEN T.SUBJECTS =
                           (SELECT DISTINCT B.SUBJECTS
                              FROM (SELECT SUBJECTS, ROWNUM A FROM SCORE) B
                             WHERE B.A = 2) THEN
                       T.SCORE
                      ELSE
                       0
                    END),
                SUM(CASE
                      WHEN T.SUBJECTS =
                           (SELECT DISTINCT B.SUBJECTS
                              FROM (SELECT SUBJECTS, ROWNUM A FROM SCORE) B
                             WHERE B.A = 3) THEN
                       T.SCORE
                      ELSE
                       0
                    END),
                SUM(CASE
                      WHEN T.SUBJECTS =
                           (SELECT DISTINCT B.SUBJECTS
                              FROM (SELECT SUBJECTS, ROWNUM A FROM SCORE) B
                             WHERE B.A = 4) THEN
                       T.SCORE
                      ELSE
                       0
                    END),
                SUM(CASE
                      WHEN T.SUBJECTS =
                           (SELECT DISTINCT B.SUBJECTS
                              FROM (SELECT SUBJECTS, ROWNUM A FROM SCORE) B
                             WHERE B.A = 5) THEN --有几个科目就写到多少
                       T.SCORE
                      ELSE
                       0
                    END)
           FROM SCORE T
          GROUP BY T.STUDENT_ID, T.GRADE

Answer 6

create or replace procedure ScoreN
as 
        subjects       varchar2(20);
                sSql varchar2(8000);
                vSql varchar2(8000);
          cursor v_cursor is select distinct subjects  from score;       
BEGIN
       sSql :='';
        --------打?游? ?sSql附case?句
        OPEN v_cursor;
         loop
           fetch v_cursor into subjects;
           EXIT WHEN v_cursor%NOTFOUND;
              sSql := sSql||' max(case subjects when '''||subjects||''' then score else 0 end) '||subjects||',';
         end loop;
         CLOSE v_cursor;
                 sSql := substr(sSql, 1, length(sSql)-1);
                 dbms_output.put_line(sSql);
            -----?行sql
      vSql :='Select student_id,grade,'||sSql||' from score group by student_id, grade';
            dbms_output.put_line(vSql);
                execute immediate vSql;
      commit;EXCEPTION
        WHEN OTHERS THEN
                ROLLBACK;
END ;

Answer 7

distinct 科目 from 表名; select student_id 学号,grade 年级,sum(math) 数学,sum(english) 英语,sum(chinese) 语文 from ( 
select student_id, 
      grade, 
      decode(subjects,'数学',score,0) math, 
      decode(subjects,'英语',score,0) english, 
      decode(subjects,'语文',score,0) chinese 
      from score 
) 
group by student_id,grade

Answer 8

第二题要写存储过程比较好实现。不然科目不确定要分多个sql去写

Answer 9

第二个有点意思，关于动态SQL语句参考该贴：http://topic.csdn.net/u/20090627/15/09a158ee-637c-4083-8220-6a4da35a4a51.html?seed=1512177817
各位可以试试  能解决的话140分奉上

Answer 10

其实这两个题目都是考CASE WHEN