WHERE子句中的COALESCE？

Question

是否有一种好的方式在SQL中表达，如果匹配了某一列，则选择特定行，否则选择更常规的行？类似于：

SELECT name FROM names 
            WHERE (colA = @colA OR colA = 'ALL') 

如果存在特定记录，我希望它只返回该记录；如果找不到特定列匹配，则返回通用的“ALL”记录。COALESCE似乎类似于我要找的东西，但我不知道在这里如何使用语法。

- flerngobot

---

这将始终返回“ALL”行，以及您指定的行。这是您想要的吗？ - Blorgbeard

我猜你可以使用 where ColA in (@colA, 'ALL')，这样会更简洁一些。 - Blorgbeard

其实这就是我的问题。我知道我这里的SELECT语句不能满足我的预期目的。不过似乎应该有一种方法可以做到这一点。 - flerngobot

Answer 1

@Blorgbeard的答案（使用top 1和order by）可能是最好的方法，但为了与众不同，您也可以使用子查询并排除不存在的内容：

SELECT name FROM names 
WHERE (colA = @colA) OR (colA = 'ALL' AND NOT EXISTS(
                                                SELECT name FROM names 
                                                WHERE colA = @colA)) 

我猜这个的“优势”是使用更标准的SQL。

- Hogan

---

如果colA='ALL'，而@colA<>'ALL'，那么colA != @colA这一部分是多余的，它将返回与原始查询完全相同的结果集，对吗？ - Blorgbeard

@Blorgbeard - 是的，你说得对，我已经删除了那段代码。 - Hogan

Answer 2

这个查询虽然不太美观，但应该能满足你的需求：

SELECT TOP 1 name FROM names 
WHERE (colA = @colA OR colA = 'ALL') 
ORDER BY CASE WHEN colA='ALL' THEN 1 ELSE 0 END

编辑：对于多列，我认为你需要的是这个：

SELECT TOP 1 name FROM names 
WHERE (colA = @colA AND colB = @colB) OR (colA = 'ALL') 
ORDER BY CASE WHEN colA='ALL' THEN 1 ELSE 0 END

我假设只有一个ALL行，因此无需检查colB='ALL'，而表的“键”是colA和colB组合 - 因此您不关心colA匹配但colB不匹配的行，例如。

- Blorgbeard

---

谢谢，这是目前为止我看到的最好的翻译。这个查询语句对于多列仍然适用吗？例如：SELECT TOP 1 name, otherName FROM names WHERE (colA=@colA OR colA='ALL') AND (colB=@colB OR colB='ALL') ORDER BY CASE WHEN colA='ALL' THEN 1 ELSE 0, CASE WHEN colB='ALL' THEN 1 ELSE 0 END - flerngobot

IE？我觉得你少了一些注释。但是，它可以适应多个“key”列，当然，多个输出列可以添加到select列表中。 - Blorgbeard

啊，你修改了它 :) colA 和 colB 是一个联合主键吗？所以，你想要具有 colA=@colA AND colB=@colB 的行，或者如果不存在这样的行，则想要 ALL 行？ - Blorgbeard

实际上，这不是复合键查找。我只是想在这些特定列没有特定匹配项时能够使用默认值。所以可能多列查找不能以这种方式工作。 - flerngobot

好的，你可能是对的。针对多列变量，提出一个新问题并且给出一些数据示例以及您希望从这些数据中得到的结果，这可能是一个不错的想法。 - Blorgbeard

Answer 3

COALESCE函数在连接多个表时非常有用，可以按照特定顺序返回第一个非空值。

例如：

SELECT
   TheID=COALESCE(Table1.TheID,Table2.TheID,NULL)
FROM
   Table1 
   INNER JOIN Table2 ON Table1.TheID=Table2.TheID

除了您发布的示例之外，实现您所要求的另一种替代方法是设置可空参数，并仅在其具有值时进行匹配：

例如：

CREATE Test(@Param1 INT=NULL)
AS
SELECT
    *
FROM
    Table
WHERE
    (@Param1 IS NULL OR IDField=@Param1)

如果所有不匹配的记录都需要一条记录以及匹配，那么我想你可以将表与自身连接以获取匹配和不匹配的记录。

SELECT
    Calc=CASE WHEN Table2.IDField IS NULL THEN Table.Value ELSE 'ALL' END
FROM
    Table
    INNER JOIN Table Table2 ON Table2.IDField<>@Param1
WHERE
    (Table.IDField=@Param1)

- Ross Bush

---

谢谢，我想避免使用可空参数。我想为该参数发送一个特定的值，并在存在该参数时匹配该参数。如果不存在该参数，则希望匹配列值为“ALL”的行。 - flerngobot

最后一个例子虽然有点笨拙，但会返回你所需的结果。 - Ross Bush