代码之家 › 专栏 › 技术社区 › user1184100

带有SET和LIKE的Sql查询显示不正确的结果

sql-server

user1184100 · 技术社区 · 7 年前

| patientFirstName | patientMiddleName | patientLastName |  startdate |    enddate |

|-------------------------|---------------------------|------------------------|--------------|------------|

|          Patient |              Demo |            Test | 2018-09-02 | 2018-09-08 |

|             John |               Doe |            Demo | 2018-09-02 | 2018-09-14 |

|            Admin |             Admin |           Admin | 2018-09-07 | 2018-09-20 |

当我运行下面的查询时,我期望只有一个记录,即“Patient Demo Test”,而不是得到两个记录。我哪里出错了?

DECLARE @searchInput varchar(10)
DECLARE @startTime DATE
DECLARE @endTime DATE
SET @searchInput = 'o'
SET @startTime = '2018-09-02'
SET @endTime = '2018-09-12'
SELECT * FROM PatientDemoTable
WHERE  (@startTime IS NULL OR startdate >= @startTime)
AND (@endTime IS NULL OR enddate <= @endTime)
AND patientFirstName like '%'+@searchInput+'%' 
OR patientMiddleName like '%'+@searchInput+'%'
OR patientLastName like '%'+@searchInput+'%'

当我移除

AND patientFirstName like '%'+@searchInput+'%' 
OR patientMiddleName like '%'+@searchInput+'%'
OR patientLastName like '%'+@searchInput+'%'

显示正确的记录,但没有文本过滤器。

http://sqlfiddle.com/#!18/db4db5/5

1 回复 | 直到 7 年前

dustytrash 7 年前

只需在适当的地方添加括号:

DECLARE @searchInput varchar(10)
DECLARE @startTime DATE
DECLARE @endTime DATE
SET @searchInput = 'o'
SET @startTime = '2018-09-02'
SET @endTime = '2018-09-12'
SELECT * FROM PatientDemoTable
WHERE  (@startTime IS NULL OR startdate >= @startTime)
AND (@endTime IS NULL OR enddate <= @endTime)
AND (patientFirstName like '%'+@searchInput+'%' 
OR patientMiddleName like '%'+@searchInput+'%'
OR patientLastName like '%'+@searchInput+'%')

推荐文章