有多个字段以上相同的SQL怎么写？

[wangxiaomax]

比如说有个客户表，指定比较5个字段，查找有4个字段以上相同的客户找出来。我比较笨，用穷举OR的方法把它找出来，请问有没有更好的写法？多谢了。此类的SQL怎么处理效率比较高。
先根据一个客户的资料如下：
identityno='1'
custname='小刘'
mobileno='13888888888'
email='[e-mail]'
phone='88123456'
去找和它类似的客户

SELECT C_CUSTNO FROM TCUSTOMER
WHERE (
(C_IDENTITYNO=identityno AND C_CUSTNAME=custname AND C_MOBILENO=mobileno AND C_EMAIL=email) OR
(C_IDENTITYNO=identityno AND C_CUSTNAME=custname AND C_MOBILENO=mobileno AND C_PHONE=phone) OR
(C_IDENTITYNO=identityno AND C_CUSTNAME=custname AND C_EMAIL=email AND C_PHONE=phone) OR
(C_IDENTITYNO=identityno AND C_MOBILENO=mobileno AND C_EMAIL=email AND C_PHONE=phone) OR
(C_CUSTNAME=custname AND C_MOBILENO=mobileno AND C_EMAIL=email AND C_PHONE=phone) OR
(C_IDENTITYNO=identityno AND C_CUSTNAME=custname AND C_MOBILENO=mobileno AND C_EMAIL=email AND C_PHONE=phone) );

[超叔csdn]

我只能想到在WHERE后面加NOT EXIST,后面列举用<>每种CASE用两个字段.这样代码会稍微短一点点.也就是形如:
WHERE NOT EXIST(
(C_IDENTITYNO<>identityno AND C_CUSTNAME<>custname) OR(...).......

[zheninchangjiang]

我的思想这想
值相同则为1否则为0，然后把这几个值加起来就行了，如结果为3 ，那就存在3个列有相同值
语法学习中，不会写

[kulama2004]

这是逻辑写法的问题，没有更好的办法写sql，除非你写一个过程可以不需要判断每种情况
{
ind := 0;

if cond1
then ind := ind + 1
...

if ind >=4 ...
}

但是sql的话你没有其他选择

[andrw]

SELECT count(*),C_CUSTNO FROM TCUSTOMER
WHERE (
(C_IDENTITYNO=identityno AND C_CUSTNAME=custname AND C_MOBILENO=mobileno AND C_EMAIL=email) OR
(C_IDENTITYNO=identityno AND C_CUSTNAME=custname AND C_MOBILENO=mobileno AND C_PHONE=phone) OR
(C_IDENTITYNO=identityno AND C_CUSTNAME=custname AND C_EMAIL=email AND C_PHONE=phone) OR
(C_IDENTITYNO=identityno AND C_MOBILENO=mobileno AND C_EMAIL=email AND C_PHONE=phone) OR
(C_CUSTNAME=custname AND C_MOBILENO=mobileno AND C_EMAIL=email AND C_PHONE=phone) OR
(C_IDENTITYNO=identityno AND C_CUSTNAME=custname AND C_MOBILENO=mobileno AND C_EMAIL=email AND C_PHONE=phone) );

[monicazhch]

学习
其实我也一直在想怎样写sql语句或者说怎样处理一个查询是合理高效率的。

[haifeng1012]

学习```````````

[超叔csdn]

我的那种效率比楼主的要高;对于楼下的思路.看能不能写成:
SELECT C_CUSTNO FROM(
select C_CUSTNO ,c1+c2+c3+c4+c5 cc
from
(select C_CUSTNO ,decode(C_IDENTITYNO,identityno,1,0) c1,
decode(C_CUSTNAME,custname,1,0) c2,
decode(C_MOBILENO,mobileno,1,0) c3,
decode(C_EMAIL,email,1,0) c4,
decode(C_PHONE,phone,1,0) c5,
from TCUSTOMER))
where cc>3

[wangxiaomax]

sbaz(万神渡劫) 写的比较有创意，我用用看。
还有比较好的想法吗？我再加些分，多谢回复的朋友。