with t as( select 1 num,'aa' str from dual union all select 2,'bb' from dual union all select 3,'cc' from dual union all select 4,'dd' from dual union all select 5,'bb ' from dual union all select 6,'dd ' from dual ) select * from t t1 where exists (select 1 from t t2 where t2.num <> t1.num and trim(t2.str) = trim(t1.str)) NUM STR ---------- --- 5 bb 6 dd 2 bb 4 dd
对 用trim 函数 没错
select 列名 from 表名 where trim(列名) in ( select trim(列名) from 表名 group by trim(列名) having count(*)>1)
------------- CREATE TABLE t( tid NUMBER, tname VARCHAR2(20) ); ------------ SELECT * FROM T WHERE TID IN (SELECT MAX(TID) FROM T T1 GROUP BY TRIM(T1.TNAME) HAVING COUNT(1) > 1); 献丑了!还是trim()
不好意思,这个有问题,更新了下。SELECT * FROM T T1 WHERE EXISTS (SELECT * FROM T WHERE TRIM(TNAME) = TRIM(T1.TNAME) HAVING COUNT(1) > 1)
with t as(
select 1 num,'aa' str from dual
union all
select 2,'bb' from dual
union all
select 3,'cc' from dual
union all
select 4,'dd' from dual
union all
select 5,'bb ' from dual
union all
select 6,'dd ' from dual
)
select *
from t t1
where exists (select 1
from t t2
where t2.num <> t1.num
and trim(t2.str) = trim(t1.str)) NUM STR
---------- ---
5 bb
6 dd
2 bb
4 dd
select trim(列名) from 表名 group by trim(列名)
having count(*)>1)
CREATE TABLE t(
tid NUMBER,
tname VARCHAR2(20)
);
------------
SELECT *
FROM T
WHERE TID IN (SELECT MAX(TID)
FROM T T1
GROUP BY TRIM(T1.TNAME)
HAVING COUNT(1) > 1);
献丑了!还是trim()
FROM T T1
WHERE EXISTS
(SELECT *
FROM T
WHERE TRIM(TNAME) = TRIM(T1.TNAME) HAVING COUNT(1) > 1)