一个较难的sql

Question

本帖最后由 maoweiting19910402 于 2012-04-23 13:50:12 编辑

Answer 1

不想具体回复了，用到几个东西：
1、行列转换
2、group by rollup(...)
3、总计部分需要用grouping，将NULL值转换为总计字样。

Answer 2

我用decode行列转换  可是有很多null出现
我该如何消除呢

Answer 3

decode完按名字Group by 一下

Answer 4

--日期','科目','科目排名','姓名','分数
WITH t AS
  (SELECT '2012/4/15' AS fdate,
    '语文'              AS subject,
    1                 AS forder,
    '毛小洋'             AS fname,
    90                AS score
  FROM dual
  UNION ALL
  SELECT '2012/4/15','数学',1,'王朱朱',94 FROM dual
  UNION ALL
  SELECT '2012/4/15','英语',1,'朱曾鞥',93 FROM dual
  UNION ALL
  SELECT '2012/4/15','语文',2,'小米',87 FROM dual
  UNION ALL
  SELECT '2012/4/15','数学',2,'小鱼',86 FROM dual
  UNION ALL
  SELECT '2012/4/15','英语',2,'小刘',89 FROM dual
  UNION ALL
  SELECT '2012/4/15','语文',3,'啊毛',81 FROM dual
  UNION ALL
  SELECT '2012/4/15','数学',3,'啊严',82 FROM dual
  UNION ALL
  SELECT '2012/4/15','英语',3,'阿里',83 FROM dual
  UNION ALL
  SELECT '2012/4/16', '语文', 1,'小米', 98 FROM dual
  UNION ALL
  SELECT '2012/4/16', '数学', 1,'王朱朱', 91 FROM dual
  UNION ALL
  SELECT '2012/4/16', '英语', 1,'朱曾鞥', 96 FROM dual
  UNION ALL
  SELECT '2012/4/16', '语文', 2,'毛小洋', 84 FROM dual
  UNION ALL
  SELECT '2012/4/16', '数学', 2,'啊严', 88 FROM dual
  UNION ALL
  SELECT '2012/4/16', '英语', 2,'阿里', 86 FROM dual
  UNION ALL
  SELECT '2012/4/16', '语文', 3,'啊毛', 80 FROM dual
  UNION ALL
  SELECT '2012/4/16', '数学', 3,'小鱼', 80 FROM dual
  UNION ALL
  SELECT '2012/4/16', '英语', 3,'小刘', 82 FROM dual
  )
--计算与上期的差值，以及排名
--DENSE_RANK是考虑到同分的情况，如果不考虑同分情况，可以直接用rank
SELECT fname,
  fdate,
  chn,
  chn-lag(chn) over(partition by fname order by fdate,chn DESC) as chnlag,
  DENSE_RANK() over(order by chn DESC) AS chnrank,
  eng,
  eng-lag(eng) over(partition by fname order by fdate,eng DESC) as englag,
  DENSE_RANK() over(order by eng DESC) AS engrank,
  mth,
  mth-lag(mth) over(partition by fname order by fdate,mth DESC) as mthlag,
  DENSE_RANK() over(order by mth DESC) AS mthrank
FROM
  (--行列转换，限于转换内容可以枚举
  SELECT fdate,
    fname,
    DECODE(subject,'语文',score,0) AS CHN,
    DECODE(subject,'英语',score,0) AS ENG,
    DECODE(subject,'数学',score,0) AS MTH
  FROM
    (    
    SELECT to_date(fdate,'yyyy/mm/dd') AS fdate,
      fname,
      subject,
      forder,
      score
    FROM t --先把FDATE改为日期型字段
    )
  )
order by fname,fdate--结果：格式有点乱
FNAME  FDATE                     CHN                    CHNLAG                 CHNRANK                ENG                    ENGLAG                 ENGRANK                MTH                    MTHLAG                 MTHRANK                
------ ------------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
啊毛   2012-04-15 00:00:00       81                                            5                      0                                             7                      0                                             7                      
啊毛   2012-04-16 00:00:00       80                     -1                     6                      0                      0                      7                      0                      0                      7                      
啊严   2012-04-15 00:00:00       0                                             7                      0                                             7                      82                                            5                      
啊严   2012-04-16 00:00:00       0                      0                      7                      0                      0                      7                      88                     6                      3                      
阿里   2012-04-15 00:00:00       0                                             7                      83                                            5                      0                                             7                      
阿里   2012-04-16 00:00:00       0                      0                      7                      86                     3                      4                      0                      0                      7                      
毛小洋 2012-04-15 00:00:00       90                                            2                      0                                             7                      0                                             7                      
毛小洋 2012-04-16 00:00:00       84                     -6                     4                      0                      0                      7                      0                      0                      7                      
王朱朱 2012-04-15 00:00:00       0                                             7                      0                                             7                      94                                            1                      
王朱朱 2012-04-16 00:00:00       0                      0                      7                      0                      0                      7                      91                     -3                     2                      
小刘   2012-04-15 00:00:00       0                                             7                      89                                            3                      0                                             7                      
小刘   2012-04-16 00:00:00       0                      0                      7                      82                     -7                     6                      0                      0                      7                      
小米   2012-04-15 00:00:00       87                                            3                      0                                             7                      0                                             7                      
小米   2012-04-16 00:00:00       98                     11                     1                      0                      0                      7                      0                      0                      7                      
小鱼   2012-04-15 00:00:00       0                                             7                      0                                             7                      86                                            4                      
小鱼   2012-04-16 00:00:00       0                      0                      7                      0                      0                      7                      80                     -6                     6                      
朱曾鞥 2012-04-15 00:00:00       0                                             7                      93                                            2                      0                                             7                      
朱曾鞥 2012-04-16 00:00:00       0                      0                      7                      96                     3                      1                      0                      0                      7                      

Answer 5

另视力2，使用SQL SERVER 2005有一个COMPUTE BY的用法，但在ORACLE 里不支持，用GROUP BY ROLLUP()语法，显示效果很差。可考虑在视图1的基础上加一个UNION ALL
select * from 视图1
union all
select fname,fdate,sum(chn),sum(chnlag),0,sum(eng),sum(englag),0,sum(mth),sum(mthlag),0 from 视图1
group by fname,fdate--这句没测试，请楼主自己搞定。
--使用UNION ALL 字段类型需要保持一致，所以后面排名对应的字段，全用0值代替。