ISNULL 在 TSQL Select 中不起作用
我有一个可以返回 null 的 TSQL SELECT。 我尝试使用 ISNULL 将其替换为 0 但由于某种原因它不起作用。 从中选择的表具有以下列：
- storeID --> int
- 惩罚百分比 --> 小数（9,2）
- penaltyDate --> dateTime
SELECT ISNULL(penaltyPercent, 0.0) AS penaltyPercent
FROM dbo.Penalty
WHERE (penaltyDate = (SELECT MAX(penaltyDate) AS date
FROM dbo.Penalty AS Penalty_1
WHERE (penaltyDate <= @date) AND (storeID = @storeID))) AND
(storeID = @storeID)
当日期早于第一个处罚日期时（此时应为 0 处罚），则不返回结果。 不知道为什么这不起作用。 我有一个解决办法，但它困扰着我。
以下是正在使用的数据示例：
storeID penaltyDate penaltyPercent
182 10/1/2008 12:00:00 AM 0.020000
182 11/1/2008 12:00:00 AM 0.040000
182 12/1/2008 12:00:00 AM 0.070000
I have a TSQL SELECT that can return a null. I tried using ISNULL to replace it with 0 but for some reason it is not working. The table selecting from has the following columns:
- storeID --> int
- penaltyPercent --> decimal(9,2)
- penaltyDate --> dateTime
SELECT ISNULL(penaltyPercent, 0.0) AS penaltyPercent
FROM dbo.Penalty
WHERE (penaltyDate = (SELECT MAX(penaltyDate) AS date
FROM dbo.Penalty AS Penalty_1
WHERE (penaltyDate <= @date) AND (storeID = @storeID))) AND
(storeID = @storeID)
When the date is before the first penalty date (when there should be 0 penalty), no result is returned. Not sure why this doesn't work. I have a work around but it is bugging me.
Here is a sample of the data being used:
storeID penaltyDate penaltyPercent
182 10/1/2008 12:00:00 AM 0.020000
182 11/1/2008 12:00:00 AM 0.040000
182 12/1/2008 12:00:00 AM 0.070000
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
当您说“当日期在第一个处罚日期之前”时，您的意思是当
@date的值小于此查询返回的值时吗？因为这样你的内部查询将返回 null，并且（如果你使用 ANSI null）这部分将返回 false，
因为与 null 的比较总是返回 null。 因此，您不是选择具有空值的行，而是不选择任何行。
附录：
要确认这是问题所在，请将外部查询的第一行更改为
It will work，因为我上面已经描述过。
When you say "When the date is before the first penalty date", do you mean when the value of
@dateis less than the value returned from this query?Because then your inner query is going to return null, and (if you're using ANSI nulls) this part will return false,
Because comparisons with null always return null. So instead of selecting one row with a null value, you're selecting no rows.
Addendum:
To confirm this is the problem, change your outer query's first line to
It will work because of what I've described above.
如果@date < 惩罚日期，则没有返回行，因此没有 ISNULL 可以操作的值。 您可能希望将惩罚百分比选择到变量中，然后选择结果集
ISNULL(@pentaltyPercent)。If @date < penaltyDate, then there is no row returned, so there is no value for ISNULL to act upon. You might want to select the penalty percent into a variable, and then select out to the result set
ISNULL(@pentaltyPercent).您是否确实拥有其日期早于第一个惩罚日期的数据？ 如果您的查询不返回记录，您的 IsNull 子句将执行任何操作，因为它对任何操作都起作用。
Do you acctually have data that it's date is before the first penaly date? If your query doesn't return records, your IsNull clause will do anything because it works against anything.