显示 TOP 1，考虑 where 条件(如果存在 -SQL 查询)

Question

我有一个案例，我正在使用如下OUTER APPLY查询

OUTER APPLY (
SELECT TOP 1 CUSTOMER_CATEGORY 
FROM   [UX_VW_CUSTOMER_DETAILS] UVFS
WHERE  UVFS.CUSTOMER_ID = ss.CUSTOMER_ID
) SFD

但是我有了新的要求，OUTER APPLY应该根据考虑customer_category = 'General'(如果存在(发生。

伪代码将如下所示

if (Any Item present in [UX_VW_CUSTOMER_DETAILS] with CUSTOMER_CATEGORY=="General' for the specific customer)
{
SELECT TOP 1 CUSTOMER_CATEGORY 
FROM   [UX_VW_CUSTOMER_DETAILS] UVFS
WHERE  UVFS.CUSTOMER_ID = ss.CUSTOMER_ID
AND UVFS.CUSTOMER_CATEGORY LIKE '%General%'
}
ELSE
{
SELECT TOP 1 CUSTOMER_CATEGORY 
FROM   [UX_VW_CUSTOMER_DETAILS] UVFS
WHERE  UVFS.CUSTOMER_ID = ss.CUSTOMER_ID
}

任何人都可以建议更好的方法来以有效的方式重写外部应用代码。

Answer 1

您可以通过在outer apply查询中添加 order by 子句来组合您的条件，以优先考虑CUSTOMER_CATEGORY = 'General'

例如

select top 1 CUSTOMER_CATEGORY
from [UX_VW_CUSTOMER_DETAILS] UVFS
where UVFS.CUSTOMER_ID = ss.CUSTOMER_ID
order by case when UVFS.CUSTOMER_CATEGORY like '%General%' then 1 else 0 end desc

当CUSTOMER_CATEGORY like '%General%'时，case 表达式的结果为 1，否则为 0。然后，我们以降序方式order by案例表达式的结果，即从高到低。总之，这意味着如果CUSTOMER_CATEGORY like '%General%'它将选择选择作为优先级。

要进一步了解其工作原理，请考虑以下方法产生的结果：

declare @Id int = 1; -- Choose a Customer ID to test with
select *
, case when UVFS.CUSTOMER_CATEGORY like '%General%' then 1 else 0 end desc OrderBy
from [UX_VW_CUSTOMER_DETAILS] UVFS
where UVFS.CUSTOMER_ID = @Id
order by case when UVFS.CUSTOMER_CATEGORY like '%General%' then 1 else 0 end desc