在SQL中将Case语句与参数/变量一起使用以检查Null值 [英] Using Case Statement in SQL with parameter/variable to check for Null values

问题描述

我正在尝试编写SQL Select语句以根据通过前端的用户输入返回记录. 我想这样写Select语句:

I am trying to write a SQL Select statement to return records based on a user input through a front end. I want to write the Select statement like this:

SELECT somefields
FROM sometable
WHERE CASE variable
         WHEN 'blank' THEN field IS NULL
         ELSE field = field
      END

基本上，我要么想过滤一列以查找NULL值，要么忽略过滤器并根据变量的值返回所有值.我知道CASE语句的结果不可执行，但是我该怎么做?

Basically I either want to filter a column to find NULL values or ignore the filter and return all values depending on the value of the variable. I know that the results of the CASE statement is not executable but how can I do this?

推荐答案

当variable为'blank'时，以下查询将为您提供field为NULL的行.如果variable是其他任何内容，它将为您提供所有行:

When variable is 'blank', the following query will give you rows where field is NULL. When variable is anything else, it will give you all rows:

SELECT somefields
FROM sometable
WHERE
    (variable = 'blank' AND field IS NULL)
    OR (variable <> 'blank')

这篇关于在SQL中将Case语句与参数/变量一起使用以检查Null值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！