显示前1个条件(如果存在)-sql查询

vom3gejh 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(330)

我有个案子，我用的是 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
}

有谁能提出更好的方法来高效地重写外部应用程序代码吗。

sql sql-server outer-join tsql outer-apply

来源：https://stackoverflow.com/questions/61926676/show-top-1-considering-where-condition-if-present-sql-query

1条答案

按热度按时间

cl25kdpy1#

您可以通过将order by子句添加到 outer apply 要排序的查询 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

case表达式的结果 CUSTOMER_CATEGORY like '%General%' 为1，否则为0。那么我们 order by 大小写表达式的结果按降序排列，即从高到低。总之，这意味着如果 CUSTOMER_CATEGORY like '%General%' 它将选择selected作为优先级。
为进一步了解其工作原理，请考虑以下结果：

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

赞(0）回复(0）举报 2021-08-09

我来回答

显示前1个条件(如果存在)-sql查询

1条答案

相关问题

热门标签

最新问答