在 Where 子句中合并? [英] COALESCE in Where clause?

问题描述

是否有一种好方法可以在 SQL 中表达在匹配列时选择特定行，否则选择更通用的行?类似的东西:

Is there a good way to express within SQL to select a specific row if a column is matched, otherwise to select a more general row? Something like:

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

我只希望它返回一个特定的记录(如果存在)，否则如果找不到特定的列匹配，则返回通用的ALL"记录.似乎 COALESCE 与我正在寻找的相似，但我不知道它在语法上如何工作.

I just want it to return the one specific record if it exists, otherwise return the generic "ALL" record if it can't find a specific column match. It seems like COALESCE would be similar to what I'm looking for, but I don't know how that could work here syntactically.

推荐答案

@Blorgbeard 回答(使用 top 1 和 order by)可能是最好的方法，但为了与众不同，您也可以使用子查询而不存在:

@Blorgbeard answer (using top 1 and order by) is probably the best way but just to be different you could also use a sub-query and not exists:

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

我猜它的优点"是它使用了更标准的 sql.

I guess the "advantage" of this is that it uses more standard sql.

这篇关于在 Where 子句中合并?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！