SELECT a.* from ttq a left join ttq b on lower(a.col1)=lower(b.col2) and lower(a.col2)=lower(b.col1) where b.col1 is not null
大小写默认是一样的,为什么要加个lower
回复2楼 ::>> 你写的SQL和我写的SQL有何区别.. 回复3楼 ::>> 我想要的结果是col1 col2 ---- ---- a b c d
select * from t_kimsung a where exists(select 1 from t_kimsung where lower(col1)=lower(a.col2) and lower(col2)=lower(a.col1)) and lower(col1)>lower(col2)
简单实现即可: SELECT L.col1, L.col2 FROM tableA L WHERE L.col1 != R.col2 在mysql中查询时,如果数据量大,尽量不要在列上使用函数,这样会大大降低效率
表中没有唯一标识的字段 SET @a=0; SET @b=0; SELECT a.* FROM (SELECT @a:=@a+1 AS pm,LOWER(col1) AS col1,LOWER(col2) AS col2 FROM ttq) a LEFT JOIN (SELECT @b:=@b+1 AS pm,LOWER(col1) AS col1,LOWER(col2) AS col2 FROM ttq) b ON LOWER(a.col1)=LOWER(b.col2) AND LOWER(a.col2)=LOWER(b.col1) AND a.pm<b.pm WHERE b.col1 IS NOT NULL
lower(a.col2)=lower(b.col1) where b.col1 is not null
::>> 你写的SQL和我写的SQL有何区别.. 回复3楼
::>>
我想要的结果是col1 col2
---- ----
a b
c d
from t_kimsung a
where exists(select 1 from t_kimsung where lower(col1)=lower(a.col2) and lower(col2)=lower(a.col1))
and lower(col1)>lower(col2)
SELECT L.col1,
L.col2
FROM tableA L
WHERE L.col1 != R.col2
在mysql中查询时,如果数据量大,尽量不要在列上使用函数,这样会大大降低效率
SET @a=0;
SET @b=0;
SELECT a.* FROM (SELECT @a:=@a+1 AS pm,LOWER(col1) AS col1,LOWER(col2) AS col2 FROM ttq) a
LEFT JOIN
(SELECT @b:=@b+1 AS pm,LOWER(col1) AS col1,LOWER(col2) AS col2 FROM ttq) b
ON
LOWER(a.col1)=LOWER(b.col2) AND LOWER(a.col2)=LOWER(b.col1) AND a.pm<b.pm
WHERE b.col1 IS NOT NULL