SQL Server where where子句中的列为null

Let's say that we have a table named Data with Id and Weather columns. Other columns in that table are not important to this problem. The Weather column can be null.

假设我们有一个名为Data with Id and Weather列的表。该表中的其他列对此问题并不重要。 “天气”列可以为null。

I want to display all rows where Weather fits a condition, but if there is a null value in weather then display null value.

我想显示Weather适合条件的所有行,但如果天气中有空值则显示空值。

My SQL so far:

到目前为止我的SQL:

SELECT * 
FROM Data d
WHERE (d.Weather LIKE '%'+COALESCE(NULLIF('',''),'sunny')+'%' OR d.Weather IS NULL)

My results are wrong, because that statement also shows values where Weather is null if condition is not correct (let's say that users mistyped wrong).

我的结果是错误的,因为如果条件不正确,那么该语句还会显示Weather为null的值(假设用户输错了错误)。

I found similar topic, but there I do not find appropriate answer. SQL WHERE clause not returning rows when field has NULL value

我发现了类似的话题,但在那里我找不到合适的答案。当字段具有NULL值时,SQL WHERE子句不返回行

Please help me out.

请帮帮我。

1 个解决方案

#1

Your query is correct for the general task of treating NULLs as a match. If you wish to suppress NULLs when there are no other results, you can add an AND EXISTS ... condition to your query, like this:

您的查询对于将NULL视为匹配的一般任务是正确的。如果您希望在没有其他结果时抑制NULL,可以在查询中添加AND EXISTS ...条件,如下所示:

SELECT * 
FROM Data d
WHERE d.Weather LIKE '%'+COALESCE(NULLIF('',''),'sunny')+'%'
   OR (d.Weather IS NULL AND EXISTS (SELECT * FROM Data dd WHERE dd.Weather LIKE '%'+COALESCE(NULLIF('',''),'sunny')+'%'))

The additional condition ensures that NULLs are treated as matches only if other matching records exist.

附加条件确保仅在存在其他匹配记录时才将NULL视为匹配。

You can also use a common table expression to avoid duplicating the query, like this:

您还可以使用公用表表达式来避免重复查询,如下所示:

WITH cte (id, weather) AS
(
    SELECT * 
    FROM Data d
    WHERE d.Weather LIKE '%'+COALESCE(NULLIF('',''),'sunny')+'%'
)
SELECT * FROM cte
UNION ALL
SELECT * FROM Data WHERE weather is NULL AND EXISTS (SELECT * FROM cte)

statement show also values where Wether is null if condition is not correct (let say that users typed wrong sunny).

声明还显示值,如果条件不正确,Wether为null(假设用户键入错误的晴天)。

This suggests that the constant 'sunny' is coming from end-user's input. If that is the case, you need to parameterize your query to avoid SQL injection attacks.

这表明恒定的“晴天”来自最终用户的输入。如果是这种情况,则需要参数化查询以避免SQL注入攻击。

#1