本人遇到的较为经典的查询语句
- 最近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;
贴出来看得更清楚一些
更新中。。。