SELECT * FROM TABLE WHERE LENGTH(LOTNO) = (SELECT MIN(LENGTH(LOTNO)) FROM TABLE );
--用下面的SQL ----如果有分组需要,在over()里面 over(partition by 分组字段) select * from (select LOTNO, LENGTH(LOTNO) L_LOTNO MIN(LENGTH(LOTNO))OVER() MIN_L_LOTNO from t) WHERE L_LOTNO = MIN_L_LOTNO;
SELECT * FROM TABLE WHERE LENGTH(LOTNO) = (SELECT MIN(LENGTH(LOTNO)) FROM TABLE);
with tmp as ( select '20101020-10R' LOTNO from dual union all select '20101020-10R-01' LOTNO from dual union all select '20101020-10R-02' LOTNO from dual union all select '20101020-10R-02-01' LOTNO from dual union all select '20101020-10R-02-01-01' LOTNO from dual union all select '20101020-10R-02-01-02' LOTNO from dual union all select '20101020-10R-02-01-03' LOTNO from dual union all select '20101020-10R-02-01-04' LOTNO from dual union all select '20101020-10R-02-02' LOTNO from dual union all select '20101020-10R-02-03' LOTNO from dual union all select '20101020-10RFAIL' LOTNO from dual union all select '20101020-20' LOTNO from dual union all select '20101020-20R-01' LOTNO from dual ) select lotno, (select count(1) from tmp b where instr(b.lotno,a.lotno)>0) col1, (select count(1) from tmp b where instr(a.lotno,b.lotno)>0) col2 from tmp a; --结果 LOTNO col1 col2 20101020-10R 11 1 20101020-10R-01 1 2 20101020-10R-02 8 2 20101020-10R-02-01 5 3 20101020-10R-02-01-01 1 4 20101020-10R-02-01-02 1 4 20101020-10R-02-01-03 1 4 20101020-10R-02-01-04 1 4 20101020-10R-02-02 1 3 20101020-10R-02-03 1 3 20101020-10RFAIL 1 2 20101020-20 2 1 20101020-20R-01 1 2
with tmp as ( select '20101020-10R' LOTNO from dual union all select '20101020-10R-01' LOTNO from dual union all select '20101020-10R-02' LOTNO from dual union all select '20101020-10R-02-01' LOTNO from dual union all select '20101020-10R-02-01-01' LOTNO from dual union all select '20101020-10R-02-01-02' LOTNO from dual union all select '20101020-10R-02-01-03' LOTNO from dual union all select '20101020-10R-02-01-04' LOTNO from dual union all select '20101020-10R-02-02' LOTNO from dual union all select '20101020-10R-02-03' LOTNO from dual union all select '20101020-10RFAIL' LOTNO from dual union all select '20101020-20' LOTNO from dual union all select '20101020-20R-01' LOTNO from dual ) select lotno from tmp a where (select count(1) from tmp b where instr(a.lotno,b.lotno)>0) = 1--结果 LOTNO 20101020-10R 20101020-20
借用樓上數據 with tmp as ( select '20101020-10R' LOTNO from dual union all select '20101020-10R-01' LOTNO from dual union all select '20101020-10R-02' LOTNO from dual union all select '20101020-10R-02-01' LOTNO from dual union all select '20101020-10R-02-01-01' LOTNO from dual union all select '20101020-10R-02-01-02' LOTNO from dual union all select '20101020-10R-02-01-03' LOTNO from dual union all select '20101020-10R-02-01-04' LOTNO from dual union all select '20101020-10R-02-02' LOTNO from dual union all select '20101020-10R-02-03' LOTNO from dual union all select '20101020-10RFAIL' LOTNO from dual union all select '20101020-20' LOTNO from dual union all select '20101020-20R-01' LOTNO from dual ) select lotno FROM tmp AS a WHERE NOT EXISTS(SELECT 1 FROM tmp WHERE LOTNO LIKE a.LOTNO||'%' AND length(LOTNO)<length(a.LOTNO))
--用下面的SQL
----如果有分组需要,在over()里面 over(partition by 分组字段)
select *
from (select LOTNO,
LENGTH(LOTNO) L_LOTNO
MIN(LENGTH(LOTNO))OVER() MIN_L_LOTNO from t)
WHERE L_LOTNO = MIN_L_LOTNO;
select '20101020-10R' LOTNO from dual
union all
select '20101020-10R-01' LOTNO from dual
union all
select '20101020-10R-02' LOTNO from dual
union all
select '20101020-10R-02-01' LOTNO from dual
union all
select '20101020-10R-02-01-01' LOTNO from dual
union all
select '20101020-10R-02-01-02' LOTNO from dual
union all
select '20101020-10R-02-01-03' LOTNO from dual
union all
select '20101020-10R-02-01-04' LOTNO from dual
union all
select '20101020-10R-02-02' LOTNO from dual
union all
select '20101020-10R-02-03' LOTNO from dual
union all
select '20101020-10RFAIL' LOTNO from dual
union all
select '20101020-20' LOTNO from dual
union all
select '20101020-20R-01' LOTNO from dual
)
select lotno,
(select count(1) from tmp b where instr(b.lotno,a.lotno)>0) col1,
(select count(1) from tmp b where instr(a.lotno,b.lotno)>0) col2
from tmp a;
--结果
LOTNO col1 col2
20101020-10R 11 1
20101020-10R-01 1 2
20101020-10R-02 8 2
20101020-10R-02-01 5 3
20101020-10R-02-01-01 1 4
20101020-10R-02-01-02 1 4
20101020-10R-02-01-03 1 4
20101020-10R-02-01-04 1 4
20101020-10R-02-02 1 3
20101020-10R-02-03 1 3
20101020-10RFAIL 1 2
20101020-20 2 1
20101020-20R-01 1 2
select '20101020-10R' LOTNO from dual
union all
select '20101020-10R-01' LOTNO from dual
union all
select '20101020-10R-02' LOTNO from dual
union all
select '20101020-10R-02-01' LOTNO from dual
union all
select '20101020-10R-02-01-01' LOTNO from dual
union all
select '20101020-10R-02-01-02' LOTNO from dual
union all
select '20101020-10R-02-01-03' LOTNO from dual
union all
select '20101020-10R-02-01-04' LOTNO from dual
union all
select '20101020-10R-02-02' LOTNO from dual
union all
select '20101020-10R-02-03' LOTNO from dual
union all
select '20101020-10RFAIL' LOTNO from dual
union all
select '20101020-20' LOTNO from dual
union all
select '20101020-20R-01' LOTNO from dual
)
select lotno
from tmp a where (select count(1) from tmp b where instr(a.lotno,b.lotno)>0) = 1--结果
LOTNO
20101020-10R
20101020-20
with tmp as (
select '20101020-10R' LOTNO from dual
union all
select '20101020-10R-01' LOTNO from dual
union all
select '20101020-10R-02' LOTNO from dual
union all
select '20101020-10R-02-01' LOTNO from dual
union all
select '20101020-10R-02-01-01' LOTNO from dual
union all
select '20101020-10R-02-01-02' LOTNO from dual
union all
select '20101020-10R-02-01-03' LOTNO from dual
union all
select '20101020-10R-02-01-04' LOTNO from dual
union all
select '20101020-10R-02-02' LOTNO from dual
union all
select '20101020-10R-02-03' LOTNO from dual
union all
select '20101020-10RFAIL' LOTNO from dual
union all
select '20101020-20' LOTNO from dual
union all
select '20101020-20R-01' LOTNO from dual
)
select lotno FROM tmp AS a WHERE NOT EXISTS(SELECT 1 FROM tmp WHERE LOTNO LIKE a.LOTNO||'%' AND length(LOTNO)<length(a.LOTNO))