22,209
社区成员
发帖
与我相关
我的任务
分享
--快
DECLARE @fcategory varchar(500) -- 搜索内容分类
DECLARE @cateName varchar(500) -- 搜索内容
declare @sqlStatment varchar(max)
declare @sortBy varchar(4000)
set @fcategory = '姓名'
set @cateName = '路遥'
set @sortBy = N' order by t1.FNumber DESC'
set @sqlStatment = N'Select
t1.id
from t_Custom t1
where
(
('''+@fcategory+'''=''客户名称'' and t1.fname like ''%'+@cateName+'%'')
or ('''+@fcategory+'''=''姓名'' and (select count(0) from t_contact t2 where t2.fcustid=t1.id and t2.fname like ''%'+@cateName+'%'')>0)
)
'
exec (@sqlStatment+@sortBy)
--------------------------
--慢
DECLARE @FCategory VARCHAR(100),@CateName VARCHAR(100)
SET @FCategory ='姓名'
SET @CateName ='路遥'
SELECT
T1.ID
FROM t_Custom T1
WHERE
(
(@FCategory = '客户名称' AND T1.FName LIKE '%'+@CateName+'%')
OR
(@FCategory = '姓名' AND (SELECT COUNT(0) FROM t_Contact K WHERE K.FCustID=T1.ID AND K.FName LIKE '%'+@CateName+'%')>0)
)
ORDER BY T1.FNumber DESC
--慢
DECLARE @FCategory VARCHAR(100),@CateName VARCHAR(100)
SET @FCategory ='姓名'
SET @CateName ='路遥'
SELECT
T1.ID
FROM t_Custom T1
WHERE
(
(@FCategory = '客户名称' AND T1.FName LIKE '%'+@CateName+'%')
OR
(@FCategory = '姓名' AND (SELECT COUNT(0) FROM t_Contact K WHERE K.FCustID=T1.ID AND K.FName LIKE '%'+@CateName+'%')>0)
)
ORDER BY T1.FNumber DESC OPTION(RECOMPILE)
DECLARE @FCategory VARCHAR(100),@CateName VARCHAR(100)
SET @FCategory ='姓名'
SET @CateName ='路遥'
IF @FCategory = '客户名称'
BEGIN
SELECT T1.ID
FROM t_Custom T1
WHERE T1.FName LIKE '%'+@CateName+'%'
ORDER BY T1.FNumber DESC
END
ELSE IF @FCategory = '姓名'
BEGIN
SELECT T1.ID
FROM t_Custom T1
WHERE EXISTS(SELECT 1 FROM t_Contact K WHERE K.FCustID=T1.ID AND K.FName LIKE '%'+@CateName+'%')
ORDER BY T1.FNumber DESC
END