SQL查询不返回任何行

Question

>part_tran_type01-01-2022<123:qwe>C>

acid	trans_id	trans_date	trans_paspecial	1tran_amt
ab500	m1
ab500	m5	10-01-2022	124：qse	20
ab500	m16	2022年1月11日	123：pyh	10	D

koen >CREATE TABLE htd (tran_id, tran_particular)AS
2  (
3  SELECT 'm1', '123:qwe' FROM DUAL UNION ALL
4  SELECT 'm5', '124:qse' FROM DUAL UNION ALL
5  SELECT 'm16', '123:pyh' FROM DUAL
6* );
Table HTD created.
koen >WITH tp_unique_vals (tran_particular, cnt) AS
2  (
3  SELECT SUBSTR(tran_particular,1,INSTR(tran_particular,':')-1), COUNT(*) 
4    FROM htd 
5    GROUP BY SUBSTR(tran_particular,1,INSTR(tran_particular,':')-1)
6  HAVING COUNT(*) = 1
7  )
8  SELECT h.* 
9    FROM htd h
10*        JOIN tp_unique_vals u ON SUBSTR(h.tran_particular,1,instr(h.tran_particular,':')-1) = u.tran_particular;
TRAN_ID    TRAN_PARTICULAR    
__________ __________________ 
m5         124:qse            
koen >

尝试使用正则表达式和聚合：

with a as (select '123:qwe' b from dual union all 
select '124:qse' from dual union all 
select '123:pyh' from dual)
select b
from a
where regexp_substr(b, '[^:]+') in ( select c 
from (select b, regexp_substr(b, '[^:]+') c 
from a)
group by c
having count(*) = 1);

返回124:qse。