sql - 基于 SQL 条件的搜索,不适用于 AND 条件
问题描述
我正在实现搜索操作(通过存储过程进行客户端搜索)我有一个“Nvarchar”类型列“ArticleHeader”和一个“DATE”列“DateEffective”我正在通过传递@operator 执行基于条件的搜索,对于我的闲置片段尝试在连词(AND)中搜索日期和文章标题我没有得到任何结果,但是如果我单独运行它们,它们会正常工作。
找到片段:
CREATE TABLE #TempItems
(
DateEffective DATE,
ArticleHeader NVARCHAR(50)
)
insert into #TempItems values ('2019-12-28','Nieuws')
insert into #TempItems values ('2020-02-12','Test')
insert into #TempItems values ('2020-01-10','zolo')
insert into #TempItems values ('2020-02-23','valued')
declare @DateEffective datetime,@operator nvarchar(10)
--set @DateEffective ='2020-01-10'
set @operator='neq'
declare @ArticleHeader nvarchar (50) ='Nieuws'
select * from #TempItems PA
Where 1=1
--------------------------------------------------------------------
And
(
(
(@operator='neq') And
CASE WHEN @DateEffective IS NULL
THEN '1900-1-1'
ELSE convert(date,PA.DateEffective)
END !=
CASE WHEN @DateEffective IS NULL
THEN '1900-1-1'
ELSE Convert(Date, @DateEffective)
End
)
OR
(
(@operator='eq') And
CASE WHEN @DateEffective IS NULL
THEN '1900-1-1'
ELSE convert(date,PA.DateEffective)
END =
CASE WHEN @DateEffective IS NULL
THEN '1900-1-1'
ELSE Convert(Date, @DateEffective)
End
)
)
----------------------------------------------------------------------
AND
(
(lower(@Operator) = 'eq' AND
CASE WHEN @ArticleHeader IS NULL
THEN '1'
ELSE PA.ArticleHeader
END =
CASE WHEN @ArticleHeader IS NULL
THEN '1'
ELSE CONVERT(nvarchar(1000), @ArticleHeader)
END
)
OR
(lower(@Operator) = 'startswith' AND
CASE WHEN @ArticleHeader IS NULL
THEN '1'
ELSE PA.ArticleHeader
END Like
CASE WHEN @ArticleHeader IS NULL
THEN '1'
ELSE CONVERT(nvarchar(1000), @ArticleHeader) + '%'
END
)
)
---------------------------------------------------------------------------
解决方案
嗨@Azhar,您能否检查一下这个查询,它在这两种情况下都运行良好。
DROP TABLE IF EXISTS #TempItems
CREATE TABLE #TempItems
(
DateEffective DATE,
ArticleHeader NVARCHAR(50)
)
insert into #TempItems values ('2019-12-28','Nieuws')
insert into #TempItems values ('2020-02-12','Test')
insert into #TempItems values ('2020-01-10','zolo')
insert into #TempItems values ('2020-02-23','valued')
insert into #TempItems values ('2020-02-24','Nieuws')
declare @DateEffective datetime,@operator nvarchar(10)
set @DateEffective ='2020-01-10'
set @operator='startswith'
declare @ArticleHeader nvarchar (50) ='Nieuws'
select * from #TempItems PA
Where 1=1
--------------------------------------------------------------------
And
(
(
(@operator='neq') And
CASE WHEN @DateEffective IS NULL
THEN '1900-1-1'
ELSE convert(date,PA.DateEffective)
END !=
CASE WHEN @DateEffective IS NULL
THEN '1900-1-1'
ELSE Convert(Date, @DateEffective)
End
)
OR
(
(@operator='eq') And
CASE WHEN @DateEffective IS NULL
THEN '1900-1-1'
ELSE convert(date,PA.DateEffective)
END =
CASE WHEN @DateEffective IS NULL
THEN '1900-1-1'
ELSE Convert(Date, @DateEffective)
End
)
OR
(
1=1
)
)
------------------------------------------------------------------------
AND
(
(lower(@Operator) = 'eq' AND
CASE WHEN @ArticleHeader IS NULL
THEN '1'
ELSE PA.ArticleHeader
END =
CASE WHEN @ArticleHeader IS NULL
THEN '1'
ELSE CONVERT(nvarchar(1000), @ArticleHeader)
END
)
OR
(lower(@Operator) = 'startswith' AND
CASE WHEN @ArticleHeader IS NULL
THEN '1'
ELSE PA.ArticleHeader
END Like
CASE WHEN @ArticleHeader IS NULL
THEN '1'
ELSE CONVERT(nvarchar(1000), @ArticleHeader) + '%'
END
)
OR
(lower(@Operator) = 'neq' AND
CASE WHEN @ArticleHeader IS NULL
THEN '1'
ELSE PA.ArticleHeader
END <>
CASE WHEN @ArticleHeader IS NULL
THEN '1'
ELSE CONVERT(nvarchar(1000), @ArticleHeader)
END
)
)
---------------------------------------------------------------------------
输出
推荐阅读
- python - 在组 Matplotlib 图中标记并使用正确颜色的图例
- numpy - 将 Fortran 数组转换为 numpy 数组
- javascript - 赠品命令不保存(Heroku)discord.js
- python - 是否无法在 web.archive “包含”网页中使用 Selenium (Python),还是我做错了什么?
- r - 在 R 中使用 xpath 抓取数据表
- c++ - 表达式必须有一个常数值 VS 2019
- javascript - Javascript 网站安全 - 全局变量
- ios - 从断开连接的蓝牙设备捕获输入时,AVAudioSession 服务重置
- r - 创建一个优化模型,以便选择开始日期和结束日期会产生一些结果
- c - 未初始化条件 Valgrind