关于ORACLE查询时排序的问题

Question

各位大哥好,
    有个问题请教各位：
     表：
     create table a(
        id int  not null,  --主键
        privice  varchar(20) not null, 省份CODE
        city varhcar(20),   --市CODE
        country varchar(20)， --县级CODE
         year int not null, --时间
        shuxuescorre number(20,4), --数学得分
        yuwenscore number(20,4)  --语文得分
     )
 
    输入的条件有， 省份，市，县。时间    如果只有全国和时间，需要统计出31个省的各个省在某个时间的总得分和排名，
     如果输入省份和时间，需要统计该省下的所有市的数学和语文总分和排名
     如果输入省份，市和时间的话，需要统计出该市下所有县的各个县的语文和数学总分级排名，    请教该SQL语句怎么写。

Answer 1

可以在程序里面判断输入的值  然后返回3个sql呢
想在一句sql里面完成？

Answer 2

--好吧，我知道我在答非所问，但个人觉得查询条件不难，难的是其它的，所以只回答了其它方面
CREATE TABLE t_test
  (
    YEAR       INT NOT NULL,
    province   VARCHAR(20) ,
    city       VARCHAR2(20),
    county     VARCHAR(20),
    scoreMaths NUMBER(20,4),
    scoreChn   NUMBER(20,4)
  );
BEGIN
  --随机生成20行数据
  FOR i IN 1..20
  LOOP
    INSERT
    INTO t_test VALUES
      (
        TRUNC(dbms_random.value(2010,2012),2),
        '42',--省编码固定
        lpad(ceil(i/4),2,'0'),--市编码根据序号取除4的整数，不足两位左边补零
        lpad(TRUNC(dbms_random.value(1,10),0),2,'0'),--县编码取1至10的随机数，不足两位，左边补零
        TRUNC(dbms_random.value(10000,50000),2),--分数取一万至五万间的随机数
        TRUNC(dbms_random.value(10000,50000),2)
      );
  END LOOP;
END;SELECT province,
  city,
  county,
  YEAR,
  RANK() OVER (partition by year Order By scoreMaths DESC) RANKMATHS,--按年分级显示排名  
  RANK() OVER (partition by year Order By scoreCHN DESC) RANKCHN
FROM
  (--先做一次分数统计
  SELECT province,
    city,
    county,
    year,
    SUM(scoremaths) AS scoreMaths,
    SUM(scoreChn)   AS scoreChn
  FROM t_test
  GROUP BY YEAR,
    province,
    city,
    county
  )
--where条件加在这里，最简单的部分，楼主自己写吧
;--排名结果
PROVINCE             CITY                 COUNTY               YEAR                   RANK                   
-------------------- -------------------- -------------------- ---------------------- ---------------------- 
42                   03                   04                   2010                   1                      
42                   04                   09                   2010                   2                      
42                   01                   09                   2011                   1                      
42                   05                   05                   2011                   2                      
42                   02                   05                   2011                   3                      
42                   04                   04                   2011                   4                      
42                   05                   06                   2011                   5                      
42                   01                   04                   2011                   6                      
42                   03                   09                   2011                   7                      
42                   05                   04                   2011                   8                      
42                   05                   08                   2011                   9                      
42                   02                   07                   2011                   10                     
42                   02                   03                   2012                   1                      
42                   01                   02                   2012                   2                      
42                   01                   05                   2012                   3                      
42                   03                   08                   2012                   4                      
42                   04                   06                   2012                   5                       17 rows selected

Answer 3

--结果贴错了。
PROVINCE             CITY                 COUNTY               YEAR                   RANKMATHS              RANKCHN                
-------------------- -------------------- -------------------- ---------------------- ---------------------- ---------------------- 
42                   04                   09                   2010                   1                      2                      
42                   03                   04                   2010                   2                      1                      
42                   02                   07                   2011                   1                      1                      
42                   05                   08                   2011                   2                      5                      
42                   05                   04                   2011                   3                      3                      
42                   03                   09                   2011                   4                      2                      
42                   01                   04                   2011                   5                      4                      
42                   05                   06                   2011                   6                      10                     
42                   04                   04                   2011                   7                      6                      
42                   02                   05                   2011                   8                      9                      
42                   05                   05                   2011                   9                      8                      
42                   01                   09                   2011                   10                     7                      
42                   04                   06                   2012                   1                      1                      
42                   03                   08                   2012                   2                      3                      
42                   01                   05                   2012                   3                      4                      
42                   01                   02                   2012                   4                      5                      
42                   02                   03                   2012                   5                      2                       17 rows selected 

Answer 4

BEGIN
  --随机生成20行数据
  FOR i IN 1..20
  LOOP
  INSERT
  INTO t_test VALUES
  (
  TRUNC(dbms_random.value(2010,2012),2),
  '42',--省编码固定
  lpad(ceil(i/4),2,'0'),--市编码根据序号取除4的整数，不足两位左边补零
  lpad(TRUNC(dbms_random.value(1,10),0),2,'0'),--县编码取1至10的随机数，不足两位，左边补零
  TRUNC(dbms_random.value(10000,50000),2),--分数取一万至五万间的随机数
  TRUNC(dbms_random.value(10000,50000),2)
  );
  END LOOP;
END;这个是存储过程主体，BEGIN前加上存储过程的定义，END改为END 存储过程名称；即可。

Answer 5

想写成一条SQL，还是拆分成3条SQL？如果是一条SQL，这个难度不是一般的大，不推荐这么玩的。
最好前台准备3个选项，全国/省/市 ，后台分拆 3个SQL。