查询需求：MSSQL查据库，先进行完整精确查询，如果有记录就直接显示，没有记录则进行模糊匹配。如何通过一条sql查询语句实现？
有一张user表。里面有个name字段，待查询关键词@name，完整查询
select * from user where name=@name
如果有值 就直接显示，没有值，则执行模糊查询
select * from user where name like '%'+@name+'%'
实现语句：
方法一
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 name=@name
UNION ALL
select *,2 grp from [user] where name like '%'+@name+'%'
),
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
