时间戳

首页 > 解决方案 > 如果字符串为空，则 T-SQL 多个 WHERE 子句

sql-server - 如果字符串为空，则 T-SQL 多个 WHERE 子句

问题描述

我有三个要过滤查询的字段。即使 1 或 2 个字段为空（传递空字符串），我也想查询。我可以找出 1 个字段，但是一旦我添加了其他两个字段，我就没有得到任何结果。花括号是我传递给查询的表单字段。

IF '{Envelope Size}' <> ''
SELECT Tools.ToolNo, 
       Tools.[Name]
FROM Tools
     LEFT JOIN
(
    SELECT AdditionalInfo.OwnerID, 
           AdditionalInfo.UserDefined3, 
           AdditionalInfo.UserDefined4, 
           AdditionalInfo.UserDefined5, 
           AdditionalInfo.UserDefined10, 
           AdditionalInfo.UserDefined24
    FROM AdditionalInfo
    WHERE AdditionalInfo.ModuleID = 35
) AS EnvStyle ON Tools.ToolID = EnvStyle.OwnerID
WHERE EnvStyle.UserDefined24 LIKE '{Envelope Size}';
ELSE
SELECT Tools.ToolNo, 
       Tools.[Name]
FROM Tools
     LEFT JOIN
(
    SELECT AdditionalInfo.OwnerID, 
           AdditionalInfo.UserDefined3, 
           AdditionalInfo.UserDefined4, 
           AdditionalInfo.UserDefined5, 
           AdditionalInfo.UserDefined12
    FROM AdditionalInfo
    WHERE AdditionalInfo.ModuleID = 35
) AS EnvStyle ON Tools.ToolID = EnvStyle.OwnerID
WHERE EnvStyle.UserDefined3 <> 'Stationery'
      AND EnvStyle.UserDefined4 = (CASE WHEN '{Env. Height}' = '' THEN 'NULL' ELSE '{Env. Height}' END)
      AND EnvStyle.UserDefined5 = (CASE WHEN '{Env. Width}' = '' THEN 'NULL' ELSE '{Env. Width}' END)
      AND EnvStyle.UserDefined12 = (CASE WHEN '{Flap Size}' = '' THEN 'NULL' ELSE '{Flap Size}' END);

标签： sql-servertsql

解决方案

我不太清楚你的数据结构是什么，花括号让我有点失望，但也许这样的东西可能对你有用。在值为空白的情况下，您可以更改过滤条件以使用ORwith a来强制匹配。CASE同样，我不确定您的结构，以下只是按照您的示例，但希望这可能会让您朝着正确的方向前进。

IF '{Envelope Size}' <> ''
SELECT Tools.ToolNo, 
       Tools.[Name]
FROM Tools
     LEFT JOIN
(
    SELECT AdditionalInfo.OwnerID, 
           AdditionalInfo.UserDefined3, 
           AdditionalInfo.UserDefined4, 
           AdditionalInfo.UserDefined5, 
           AdditionalInfo.UserDefined10, 
           AdditionalInfo.UserDefined24
    FROM AdditionalInfo
    WHERE AdditionalInfo.ModuleID = 35
) AS EnvStyle ON Tools.ToolID = EnvStyle.OwnerID
WHERE EnvStyle.UserDefined24 LIKE '{Envelope Size}';
ELSE
SELECT Tools.ToolNo, 
       Tools.[Name]
FROM Tools
     LEFT JOIN
(
    SELECT AdditionalInfo.OwnerID, 
           AdditionalInfo.UserDefined3, 
           AdditionalInfo.UserDefined4, 
           AdditionalInfo.UserDefined5, 
           AdditionalInfo.UserDefined12
    FROM AdditionalInfo
    WHERE AdditionalInfo.ModuleID = 35
) AS EnvStyle ON Tools.ToolID = EnvStyle.OwnerID
WHERE EnvStyle.UserDefined3 <> 'Stationery'
      AND (EnvStyle.UserDefined4 = {Env.Height} 
            OR 1 = CASE WHEN {Env.Height} = '' THEN 1 ELSE 0 END)
      AND (EnvStyle.UserDefined5 = {Env.Width} 
            OR 1 = CASE WHEN {Env.Width} = '' THEN 1 ELSE 0 END)
      AND (EnvStyle.UserDefined12 = {Flap Size} 
            OR 1 = CASE WHEN {Flap Size} = '' THEN 1 ELSE 0 END);

推荐阅读