比如说有个客户表,指定比较5个字段,查找有4个字段以上相同的客户找出来。我比较笨,用穷举OR的方法把它找出来,请问有没有更好的写法?多谢了。此类的SQL怎么处理效率比较高。
先根据一个客户的资料如下:
identityno='1'
custname='小刘'
mobileno='13888888888'
email='[email protected]'
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) );
先根据一个客户的资料如下:
identityno='1'
custname='小刘'
mobileno='13888888888'
email='[email protected]'
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) );
解决方案 »
- 不知道密码的情况下如何备份数据库
- 执行SQL报ORA-03113: end-of-file on communication channel错误,各位大侠,请帮忙看看!
- oracle 不能插入值,请问是什么原因
- 救命呀,关于10g em的问题,郁闷死,搞了好多天了还是不行
- 100分,应该是一个非常简单的脚本问题,谢谢大家了!
- 征询standby热备数据库的创建步骤?
- linux中如何实现访问oracle的pro*c/c++接口封装
- 急,怎么用system登录不了oracle9i?
- 谁能帮忙看看这个存储过程错在那里?
- PL/SQL 问题,我用不等于查询,却找不到为空值的那些行!!!!
- 请教怎么计算一个表的占用空间?
- 刚刚学习Oracle,SQL Server 中的这条sql语句,在Oracle中怎么写阿?
WHERE NOT EXIST(
(C_IDENTITYNO<>identityno AND C_CUSTNAME<>custname) OR(...).......
{
ind := 0;if cond1
then ind := ind + 1
...if ind >=4 ...
}但是sql的话你没有其他选择
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) );
其实我也一直在想怎样写sql语句或者说怎样处理一个查询是合理高效率的。
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
还有比较好的想法吗?我再加些分,多谢回复的朋友。