表格一列的三个条件 [英] Three conditions on one column of table

问题描述

以下是示例表的脚本

DECLARE @tbl TABLE (ID int)
INSERT INTO @tbl VALUES(1), (2), (NULL), (3), (NULL), (1)

我想要列 ID

SELECT * FROM @tbl WHERE ID -- Can't figure out

1. 如果用户想要所有行
2. 如果用户想要 ID 为 NULL 的行
3. 如果用户想要 ID 不为空的行

我可以通过将查询放在字符串中来做到这一点，但在长查询中只有一个这样的条件，所以我不想将所有查询放在字符串中.

I can do this by putting my query in string but In long query there is only one condition like this so I don't want to put all query in string.

回应@Tim Schmelter.我很抱歉我无法表达我的观点.用户将从前端选择他/她想要所有行，仅指定 ID 的行或未指定 ID 的行

In response to @Tim Schmelter. My apologies that I could not make my point clear. User will select from front end that either s/he want to all rows only rows where ID is given or rows where ID is not given

在长查询中，一个条件是这样的

In long query one condition is like this

@id INT // Value from front end like 'All', 'Products', 'No Products'
WHERE ID = @ID // Here I can't figure out that how to use one of three conditions

如果我仍然无法解释我的问题，请告诉我.我会再试一次.

Please let me know If I am still unable to explain my question. I will try again.

谢谢.

推荐答案

您可以向用户呈现选项(例如单选按钮)，然后将所选选项的值传递给数据库，例如:

You can present to user the choices(e.g. radio button), then pass the chosen option's value to database like :

SELECT * FROM @tbl 
WHERE 
   (@Option = 1)
   OR (@Option = 2 AND ID IS NULL)
   OR (@Option = 3 AND ID IS NOT NULL)

OR 可能会降低性能，但 SQL Server(或任何与此相关的 RDBMS)不会短路.什么时候可以有些强制短路

OR can kill performance though, SQL Server(or any RDBMS for that matter) doesn't do short-circuit. CASE WHEN can somewhat force short-circuit

SELECT * FROM @tbl 
WHERE 
   CASE @Option
   WHEN 1 THEN 1
   WHEN 2 THEN 
          CASE WHEN ID IS NULL THEN 1 END
   WHEN 3 THEN
          CASE WHEN ID IS NOT NULL THEN 1 END
   END = 1

这篇关于表格一列的三个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！