oracle 先精确再模糊查找,Sql语句：先精确匹配再模糊查询

查询需求：MSSQL查据库，先进行完整精确查询，如果有记录就直接显示，没有记录则进行模糊匹配。如何通过一条sql查询语句实现？

有一张user表。里面有个name字段，[email protected]，完整查询

select * from user where [email protected]

如果有值 就直接显示，没有值，则执行模糊查询

select * from user where name like [email protected]+'%'

实现语句：

方法一

with table1 as

(

select 'a' [name] union all

select 'bac' [name] union all

select 'b' [name]

)

select * from table1

where [name] like case when (select COUNT(*) from table1 where [name]='a') >0 then 'a' else '%a%' end

方法二

-- 参数

DECLARE @name varchar(20)

SET @name = 'x'

-- 一条sql搞定

;WITH

/* 测试数据

[user](id,[name]) as

(

SELECT 1,'x' UNION ALL

SELECT 2,'y' UNION ALL

SELECT 3,'xyz' UNION ALL

SELECT 4,'zyx'

),*/

a AS (

select *,1 grp from [user] where [email protected]

UNION ALL

select *,2 grp from [user] where name like [email protected]+'%'

),

b AS (

SELECT *,

RANK() OVER(ORDER BY grp) rank

FROM a

)

SELECT id,[name]

FROM b

WHERE rank = 1

@name = 'x' 的结果

id name

----------- ----

1 x

@name = 'z' 的结果

id name

----------- ----

3 xyz

4 zyx

个人觉得，第二种方法在先精确查询再模糊查询中效率更高。

声明:如需转载，请注明来源于www.webym.net并保留原文链接：http://www.webym.net/jiaocheng/1087.html