oracle中几个经典的查询语句

本人遇到的较为经典的查询语句

• 最近7天

SELECT Rownum,(trunc(sysdate + Rownum-7,‘dd’)) past7 FROM dual CONNECT BY rownum <= 7;

• 最近24小时

SELECT Rownum,trunc(sysdate,‘hh’) + rownum / 24 past24 FROM dual CONNECT BY rownum<=24–稍快推荐使用

• 当月

SELECT TRUNC(SYSDATE, ‘MM’) + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd’))

• 查询未来半年的所有月份

SELECT to_char(add_months(SYSDATE, ROWNUM), ‘yyyy-mm’) FROM dual CONNECT BY ROWNUM <= 6

• 删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录

DELETE from 表名 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表名 GROUP BY id HAVING COUNT(*) > 1);

• 查询以数字开头，以XXX结尾的数据

SELECT * FROM 表名 a WHERE regexp_like(a字段名,'^[[:digit:]]*XXX$');

• 查询某个字段中包含XXX字符串的数据

SELECT * FROM 表名 WHERE dbms_lob.instr(字段名，‘XXX’) > 0

• case when 举例

SELECT ID,NAME,
CASE WHEN NAME IN (‘审核中’,‘询价中’) THEN ‘评估中’
ELSE ‘已完成’
END AS STATUS
FROM TESTS;

贴出来看得更清楚一些

更新中。。。