17 oracle其它实用技巧（2）

1.插入全年的日期

INSERT INTO TAB
SELECT TO_DATE('20190101','YYYYMMDD')+ROWNUM-1 FROM ALL_OBJECTS
WHERE ROWNUM<= TO_CHAR(TO_DATE('20191231','YYYYMMDD'),'DDD')

2.找到某列第N大的记录

SELECT * FROM
(SELECT SNO 学号 ,SNAME 姓名 ,SAGE 年龄,DENSE_RANK()
OVER (ORDER BY SAGE DESC) RANK FROM STU)
WHERE RANK = 3;
--row_number(),rank(),DENSE_RANK() 以及分组partition by
--下图截图一
select NO 学号 ,NAME 姓名 ,NAGE 年龄,row_number() over(partition by calss order by NAGE desc) rank from TEST0220;
--下图截图二
select NO 学号 ,NAME 姓名 ,NAGE 年龄,rank() over(partition by calss order by NAGE desc) rank from TEST0220;
--下图截图三
SELECT NO 学号 ,NAME 姓名 ,NAGE 年龄,DENSE_RANK() OVER (partition by calss ORDER BY NAGE DESC) RANK FROM TEST0220;
--不使用函数的解决方案
--找出各班年龄最小的三个人
select NO 学号 ,NAME 姓名 ,NAGE 年龄,calss from TEST0220 c where
(select count(distinct a.NAGE) from TEST0220 a where a.NAGE<c.NAGE and a.calss=c.calss)<3;

注：除dense_rank(),另外还有row_number(),rank()排序,注意Parttion by。区别在于：

• row_number() over()【截图一结果】为查询出来的每一行记录生成一个序号，依次排序且不会重复；
• rank() over()【截图二结果】为相同排序字段生成相同序号，依次排序，不相同的序号将跳过相同的排名号排下一个；
• dense_rank() over()【截图三结果】为相同排序字段生成相同序号，依次排序，不相同的序号将紧邻相同的排名号。

更具体详情见其它博文。或其它具体示例以及解决方案见：LeetCode数据库部门工资前三高的员工。

3.其它综合操作（TRUNC(N/3600) || '小时' || TO_CHAR(TO_DATE(MOD(N,3600),'SSSSS'),'FMMI"分"SS"秒"')）

--返回记录行的物理地址
SELECT ROWID 物理地址,NO,NAME FROM STU;
--获取重复的年龄的值
SELECT NAGE from TEST0220 GROUP BY NAGE HAVING COUNT(*)>1;
--找到一列重复值(继而可以进行删除操作)
SELECT MIN(ROWID) from TEST0220 GROUP BY NAGE HAVING COUNT(*)>1;
--秒转换为时分秒
DECLARE
N NUMBER := 1000000;
RET VARCHAR2(100);
BEGIN
RET := TRUNC(N/3600) || '小时' || TO_CHAR(TO_DATE(MOD(N,3600),'SSSSS'),'FMMI"分"SS"秒"');
DBMS_OUTPUT.PUT_LINE(RET);
END;

*ROWID是唯一标志记录物理位置的一个ID值，其由18位十六进制数组成。其中，前6位表示数据对象编号，第7~9位表示相对表空间的数据文件号，第10~15位表示这条记录在数据文件中的第几个BLOCK（块）中，最后3位表示这条记录是BLOCK（块）中的第几条记录。