oracle中构造序列

Oracle Row Generator Techniques

http://www.club-oracle.com/forums/row-generation-method-in-sql-also-in-pl-sql-t129/

http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques
早期使用----- select rownum from all_objects where rownum <xxx

现在可以使用-----select rownum from dual connect by rownum<xxx

或者----select level from dual connect by level<xxx

要实现当前年份的前五年至后十年的下拉列表，方式1

 

SELECT DECODE(B.ROWN,
              1,
              COUNTS - 5,
              2,
              COUNTS - 4,
              3,
              COUNTS - 3,
              4,
              COUNTS - 2,
              5,
              COUNTS - 1,
              6,
              COUNTS,
              7,
              COUNTS + 1,
              8,
              COUNTS + 2,
              9,
              COUNTS + 3,
              10,
              COUNTS + 4,
              11,
              COUNTS + 5)
  FROM (SELECT TO_CHAR(SYSDATE, 'YYYY') AS COUNTS FROM DUAL) A,
       (SELECT ROWNUM ROWN FROM ALL_OBJECTS WHERE ROWNUM <= 11) B

要实现当前年份的前五年至后十年的下拉列表，方式2

 

SELECT TO_CHAR(SYSDATE, 'yyyy') - (5 - NUMS)
  FROM (SELECT ROWNUM NUMS FROM ALL_OBJECTS WHERE ROWNUM < 6)
UNION ALL
SELECT TO_CHAR(SYSDATE, 'yyyy') + NUMS
  FROM (SELECT ROWNUM NUMS FROM ALL_OBJECTS WHERE ROWNUM < 6)

考试题：

查询一个表中相同的名字的人的个数(使用名字进行分组，假如没有重名的，则它的count值为1.判断为1的时候，进行减1

运算，当不为1的时候，不进行减法)

 

SELECT SUM(COUNTS)
  FROM (SELECT CASE COUNT(*)
                 WHEN 1 THEN
                  COUNT(*) - 1
                 ELSE
                  COUNT(*)
               END COUNTS
          FROM A
         GROUP BY A.NAMES)

查询每个月注册的用户的总数

 

SELECT COUNT(*), BB.MM
  FROM (SELECT B.*, TO_CHAR(B.CREATE_TIME, 'MM') MM FROM B) BB
 GROUP BY BB.MM
 ORDER BY BB.MM

查询在某一区间的数据

 

SELECT *
  FROM (SELECT ROWNUM AS NUMS, A.*
          FROM (SELECT * FROM EMP E ORDER BY E.EMPNO) A) B
 WHERE B.NUMS > 7;