带有条件where的sql查询有时只起作用
我正在基于数据库中的 SQL 存储过程创建一个报表（在 Crystal Reports XI 中）。该查询接受一些参数，并返回指定日期范围内的记录。如果传入参数，它们将用于确定要返回哪些记录。如果未传入一个或多个参数，则该字段不会用于限制返回的记录类型。这有点复杂，所以这是我的 WHERE 子句：
WHERE ((Date > @start_date) AND (Date < @end_date))
AND (@EmployeeID IS NULL OR emp_id = @EmployeeID)
AND (@ClientID IS NULL OR client_id = @ClientID)
AND (@ProjectID IS NULL OR project_id = @ProjectID)
AND (@Group IS NULL OR group = @Group)
现在，针对问题：
查询（和报告）对于 2000-2005 年范围内的旧数据运行良好。然而，WHERE 子句最近几年并没有正确过滤数据：它只返回参数@Group 为NULL（即：未传入）的记录。
如有任何提示、技巧或线索，我们将不胜感激！
I'm creating a report (in Crystal Reports XI) based on a SQL stored procedure in a database. The query accepts a few parameters, and returns records within the specified date range. If parameters are passed in, they are used to determine which records to return. If one or more parameters are not passed in, that field is not used to limit the types of records returned. It's a bit complicated, so here's my WHERE clause:
WHERE ((Date > @start_date) AND (Date < @end_date))
AND (@EmployeeID IS NULL OR emp_id = @EmployeeID)
AND (@ClientID IS NULL OR client_id = @ClientID)
AND (@ProjectID IS NULL OR project_id = @ProjectID)
AND (@Group IS NULL OR group = @Group)
Now, for the problem:
The query (and report) works beautifully for old data, within the range of years 2000-2005. However, the WHERE clause is not filtering the data properly for more recent years: it only returns records where the parameter @Group is NULL (ie: not passed in).
Any hints, tips, or leads are appreciated!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
解决了！
毕竟，它实际上与 WHERE 子句无关。我让 SQL Server 为我生成一个内部联接，这应该是一个左联接：近年来的许多记录不包含联接表（费用）中的条目，因此它们没有显示。有趣的是，在费用表中确实有条目的少数最近记录的组具有 NULL 值，这就是为什么我仅在 @Group 为 NULL 时才获得记录。
这个故事的寓意是： 1. 仔细检查自动生成的任何内容； 2. 留意 NULL 值！ （n8wl - 感谢您给我仔细观察 NULL 的提示。）
Solved!
It actually had nothing to do with the WHERE clause, after all. I had let SQL Server generate an inner join for me, which should have been a LEFT join: many records from recent years do not contain entries in the joined table (expenses), so they weren't showing up. Interestingly, the few recent records that do have entries in the expenses table have a NULL value for group, which is why I got records only when @Group was NULL.
Morals of the story: 1. Double check anything that is automatically generated; and 2. Look out for NULL values! (n8wl - thanks for giving me the hint to look closely at NULLs.)
您的较新数据（2005 年之后）在 emp_id、client_id、project 中有一些带有 NULL 的行的可能性有多大
_id，还是组？如果它们是 NULL，则它们无法匹配您传递的参数。
What are the chances that your newer data (post-2005) has some rows with NULL's in emp_id, client_id, project
_id, or group? If they were NULL's they can't match the parameters you're passing.
由于日期和组是保留字，您可以尝试在字段两边加上方括号，这样它们就不会被处理。这样做可以摆脱像这样的“奇怪”问题。这样就可以了：
Since Date and group are reserved words you might try putting square brackets around the fields so they aren't processed. Doing so can get rid of "odd" issues like this. So that would make it: