VB.NET SQL 查询空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16240362/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VB.NET SQL Query Null value
提问by Pakk
So my query is this
所以我的查询是这样的
SELECT pm,SiteNumber,Client,OnHold,Urgent,SARequired,MissingInformation FROM Sites WHERE OnHold<>'' or Urgent<>'' or MissingInformation<>'' or SARequired<>'' and PM='K' and PM is not null and (ProjectStatus<>'Complete' or ProjectStatus<>'Archived') Order By SiteNumber asc
where PM is not Null - I have also tried in vb.net to set the value to
其中 PM 不是 Null - 我也在 vb.net 中尝试将值设置为
PM<>dbnull.value
'also tried
PM<> is null
'or just
PM<> Null
None of them seem to leave out the nulls
他们似乎都没有遗漏空值
also confusing me that I'm asking to only see PM where PM='K' as in the query above but I see 'R'
也让我感到困惑的是，我只要求在上面的查询中看到 PM where PM='K' 但我看到的是 'R'
Here is what I'm getting back
这是我要回来的
pm
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
R
NULL
NULL
NULL
Yes on purpose I tested 'K' for the PM Column since I know I don't have any
是的，我特意为 PM 列测试了“K”，因为我知道我没有
I'm sure its something stupid simple , just can't put my finger on it
我敢肯定它是一些愚蠢的简单的东西，只是不能把我的手指放在上面
Any help is appreciated
任何帮助表示赞赏
回答by Tim Schmelter
Just use IS NOT NULL. But the problem in your query are the ORs.
只需使用IS NOT NULL. 但是您查询中的问题是ORs。
formatted:
格式化：
SELECT columns
FROM sites
WHERE onhold <> ''
OR urgent <> ''
OR missinginformation <> ''
OR sarequired <> ''
AND pm = 'K'
AND pm IS NOT NULL
AND ( projectstatus <> 'Complete'
OR projectstatus <> 'Archived' )
ORDER BY sitenumber ASC
If you want to exclude NULLSin any case put the rest of the conditions in parentheses:
如果您想NULLS在任何情况下排除，请将其余条件放在括号中：
SELECT columns
FROM sites
WHERE pm IS NOT NULL
AND ( onhold <> ''
OR urgent <> ''
OR missinginformation <> ''
OR sarequired <> ''
AND pm = 'K'
AND ( projectstatus <> 'Complete'
OR projectstatus <> 'Archived' ) )
ORDER BY sitenumber ASC
Edit: how can we fix the R showing up instead of K when i tell it to look for K not R
编辑：当我告诉它寻找 K 而不是 R 时，我们如何修复显示的 R 而不是 K
I must have overlooked that question. When you only want to have rows with pm = 'K'you don't need to ask for non-null rows at all because pm = 'K'is not-null implicitly:
我一定是忽略了这个问题。当您只想拥有行时，pm = 'K'您根本不需要要求非空行，因为pm = 'K'隐式非空：
WHERE pm = 'K'
AND ( onhold <> '' ...
回答by Gordon Linoff
You need parentheses around the initial orconditions in the whereclause.
您需要or在where子句中的初始条件周围加上括号。
SELECT pm,SiteNumber,Client,OnHold,Urgent,SARequired,MissingInformation
FROM Sites
WHERE (OnHold<>'' or Urgent<>'' or MissingInformation<>'' or SARequired<>'') and
PM='K' and PM is not null and (ProjectStatus<>'Complete' or ProjectStatus<>'Archived')
Order By SiteNumber asc
In SQL, ANDhas higher precedence than OR. So, the original statement was parsing as:
在 SQL 中，AND优先级高于OR. 因此，原始语句解析为：
SELECT pm,SiteNumber,Client,OnHold,Urgent,SARequired,MissingInformation
FROM Sites
WHERE OnHold<>'' or Urgent<>'' or MissingInformation<>'' or
(SARequired<>'' and PM='K' and PM is not null and (ProjectStatus<>'Complete' or ProjectStatus<>'Archived'))
Order By SiteNumber asc
