考虑到查询的简单性，以及使用单个 @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;