如何让这个 SQL 语句返回空字符串而不是 NULL？
LastAccessed=(select max(modifydate) from scormtrackings WHERE
bundleid=@bundleid and userid=u.userid),
CompletedLessons=(select Value from scormtrackings WHERE
bundleid=@bundleid and userid=u.userid AND param='vegas2.progress'),
TotalLessons=100,
TotalNumAvail=100,
TotalNumCorrect=(SELECT Value FROM scormtrackings WHERE
bundleid=@bundleid AND userid=u.userid AND param='cmi.score.raw')
这只是我的 ASP.NET Repeater 使用的大型选择语句的一部分，当值为 NULL 时，该语句不断崩溃，我尝试过 ISNULL() 但要么它不起作用，要么我做错了。
ISNULL((SELECT max(modifydate) FROM scormtrackings WHERE
bundleid=@bundleid AND userid=u.userid),'') AS LastAccessed,
(...)
???
更新：我已经尝试了所有这些方法，返回 '', 0, 1，而不是返回 null 的值，但它仍然不起作用，我想知道问题是否出在 Repeater ？
相关问题：
LastAccessed=(select max(modifydate) from scormtrackings WHERE
bundleid=@bundleid and userid=u.userid),
CompletedLessons=(select Value from scormtrackings WHERE
bundleid=@bundleid and userid=u.userid AND param='vegas2.progress'),
TotalLessons=100,
TotalNumAvail=100,
TotalNumCorrect=(SELECT Value FROM scormtrackings WHERE
bundleid=@bundleid AND userid=u.userid AND param='cmi.score.raw')
This is only part of a large select statement used by my ASP.NET Repeater that keeps crashing when the values are NULL, I have tried ISNULL() but either it didn't work, or I did it wrong.
ISNULL((SELECT max(modifydate) FROM scormtrackings WHERE
bundleid=@bundleid AND userid=u.userid),'') AS LastAccessed,
(...)
???
UPDATE: I've tried all these things with returning '', 0, 1, instead of the value that would be null and it still doesn't work, I wonder if the problem is with the Repeater?
Related Question:
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您可以使用 COALESCE 函数来避免获取空值。 基本上它返回列表中的第一个非空值。
这是一个 ANSI 标准，但我注意到它在 Access SQL 中不可用。
You can use the COALESCE function to avoid getting nulls. Basically it returns the first non-null value from the list.
This is an ANSI standard, though I have noticed that it isn't available in Access SQL.
您可以使用案例：
You can use CASE:
只要至少有一行满足 where 子句就可以工作，否则您仍然会得到 NULL
应该在所有情况下都可以工作
will work as long as there is at least one row that satisfies the where clause, otherwise you will still get NULL
should work in all cases
您可以使用
NVL。NVL(<可能的 null 值>,) You can use
NVL.NVL(<possible null value>,<value to return when arg1 is null>)这也很有效...不愿承认我使用它的频率！！
声明 @LastAccessed varchar(30)
从 scormtrackings 选择 @LastAccessed = max(modifydate)
设置 @LastAccessed = isnull(@LastAccessed,'')
选择 @LastAccessed 作为 LastAccessed
This works too... hate to admit how often I use it!!
Declare @LastAccessed varchar(30)
Select @LastAccessed = max(modifydate) from scormtrackings
Set @LastAccessed = isnull(@LastAccessed,'')
Select @LastAccessed as LastAccessed