- 在查询的结果中进行查询
select *from( select tb.A ,substr(tb.A,9) as NN from tb ) where NN like ‘xxx%’
例如:
SELECT *FROM ( select box_id, substr(box_id,9) as barcode from suittag ) where barcode like ‘2755%’
其中substr(string str, int startIndex )截取字符串,其下标是从1开始,不是0,也可以测试一下
模糊匹配
like xxx%’ ----表示匹配以xxx开头字符串
like ‘%xxx%’—表示匹配包含‘xxx’的字符串。
like ‘%xxx’—表示匹配以‘xxx’结尾的字符串
as----别名。可以方面再次查询时,在where子句中使用。
- 查找一个表中某个字段的统计值,可以用于判断是否有重复的数据
查出字段AA的重复项
select from
( select AA as filedA ,count() as num from tb where 条件 group by AA ) tb_new
where num>1;
例如:
select * from ( select QrShort as dd , count(*) as num from data where keyid=‘2008070816044218’ group by QrShort ) tb where num>1
或者
select QrShort from data where keyid=‘2008070816044218’ group by QrShort having count(QrShort)>1
查出字段A重复项的完整记录,使用 in 关键字。
例如:
select rowid, from data where QrShort in
(
select tb.dd from ( select QrShort as dd , count() as num from data where keyid=‘2008070816044218’ group by QrShort ) tb where num>1);
也可以使用。group by 与 having 连用
select * from data where QrShort in ( select QrShort from data where keyid=‘2008070816044218’ group by QrShort having count(QrShort)>1)
- oracle 字符串与日期转换
将字符串转成日期
select to_date(‘2005-01-01 13:14:20’,‘yyyy-MM-dd HH24:mi:ss’) from dual;
select to_date(‘2020-04-23’,‘yyyy-MM-dd’) from dual;
例如:
select *from plan_history where batch_behind=‘03’ and to_date(I_DATE,‘yyyy-MM-dd’)>add_months(sysdate,-6) order by to_date(I_DATE,‘yyyy-MM-dd’) desc;
to_date() 函数使用
add_months(sysdate,-6),系统当前时间的过去6个月
- oracle 实现limit 效果
select *from( select 字段 from 表 where 子句 )where ROWNUM<10;
例如:
select *from(select * from plan_history where batch_behind=’" + classID + "’ order by PRODUCT_BILL desc) where ROWNUM<10