时间戳

首页 > 解决方案 > 多个 IF EXISTS 语句以提高级别

sql - 多个 IF EXISTS 语句以提高级别

问题描述

我有以下查询，如果它存在则选择一个特定的值，如果它不存在，它会上升一个级别，否则它会选择另一个记录。

IF EXISTS (SELECT top 1 * from tblDiscounts where ItemType_Fkey = 5176 order by EntryDate desc) 
BEGIN
    SELECT top 1 * from tblDiscounts where ItemType_Fkey = 5176 order by EntryDate desc
END    
ELSE IF EXISTS (SELECT discount_fkey from tblItems where ID = (select Item_Fkey from tblItemType where ID = 5176)) 
BEGIN
    select * from tblDiscounts where ID = (SELECT discount_fkey from tblItems where ID = (select Item_Fkey from tblItemType where ID = 5176))
END
ELSE
BEGIN
    select top 1 * from tblDiscounts where ID NOT IN (select Discount_Fkey from tblItems) and ItemType_Fkey is null
END

我认为这个查询会显着降低性能。有没有更好的方法来实现更有效的相同结果？希望有人引导我朝着正确的方向前进。

标签： sqlsql-server

解决方案

考虑到您的查询的简单性，以及您用一个单一的方式调用它的事实，@id是否需要提高性能是有争议的。然而，鉴于 T-SQL 更适合基于集合的解决方案，解决此类问题的一种方法是首先使用order by获得最高优先级的结果，例如

select top 1 *
from tblDiscounts
order by
    -- Highest priority condition
    case when ItemType_Fkey = @Id then 1 else 0 end desc
    -- Second highest priority condition
    , case when ID = (select discount_fkey from tblItems where ID = (select Item_Fkey from tblItemType where ID = @Id)) then 1 else 0 end desc
    -- Third highest priority condition
    , case when ID not in (select Discount_Fkey from tblItems) and ItemType_Fkey is null then 1 else 0 end
    -- And then use the latest one matching our criteria
    , EntryDate desc;

为了调试它，将order by计算放在查询中并删除，top 1因为这将允许您查看逻辑工作并在必要时对其进行调整，例如

select *
    -- Highest priority condition
    , case when ItemType_Fkey = @Id then 1 else 0 end
    -- Second highest priority condition
    , case when ID = (select discount_fkey from tblItems where ID = (select Item_Fkey from tblItemType where ID = @Id)) then 1 else 0 end
    -- Third highest priority condition
    , case when ID not in (select Discount_Fkey from tblItems) and ItemType_Fkey is null then 1 else 0 end
    -- And then use the latest one matching our criteria
    , EntryDate
from tblDiscounts
order by
    -- Highest priority condition
    case when ItemType_Fkey = @Id then 1 else 0 end desc
    -- Second highest priority condition
    , case when ID = (select discount_fkey from tblItems where ID = (select Item_Fkey from tblItemType where ID = @Id)) then 1 else 0 end desc
    -- Third highest priority condition
    , case when ID not in (select Discount_Fkey from tblItems) and ItemType_Fkey is null then 1 else 0 end desc
    -- And then use the latest one matching our criteria
    , EntryDate desc;

推荐阅读