如何在SQL查询中处理OR条件，优先选取指定Event_Code记录

需求说明 #

从存储用户请求的表中获取记录，每个请求由[Bakasha_Number]字段标识，一个请求可对应多条不同[Event_Code]的记录（代表不同流程阶段）。要求：

• 筛选出[Event_Code]为1100或1101的记录
• 当同一[Bakasha_Number]同时存在这两个Event_Code时，仅保留1100的记录

原查询（含语法错误） #

WITH 
    irelevant as (
    SELECT [Entity_Number] from  [HFA_M].[dbo].[IB_Entity_Events]
    where Event_Code in ('1552', '1567')) -- כל הבקשות שקיבלו קוד 1552,1567 לסינון בהמשך השאילתה

SELECT 
       ROW_NUMBER() OVER(ORDER BY [IB_Bakasha].[Tik_Binian] ASC) AS OID
      ,[IB_Bakasha].[Tik_Binian]
      ,[IB_Bakasha].[Bakasha_Number]
      ,[IB_Bakasha].[Bakasha_Description]
      ,[IB_Bakasha].[Ikari_Area]
      ,[IB_Bakasha].[Y_Diur]
      ,[IB_Bakasha].[Floors_Requested_Number]
      ,[IB_Bakasha].[Basement_Floors]
      ,[IB_Entity_Events].[Entity_Number]
      ,[IB_Entity_Events].[Event_Code]
      ,[IB_Entity_Events].[Event_Date]

      FROM [HFA_M].[dbo].[IB_Bakasha]
          -- 原语法错误：少了一个闭合方括号
          join [HFA_M].[dbo].[IB_Entity_Events] on [Bakasha_Number] = [IB_Entity_Events].[Entity_Number]
      where 
      [IB_Bakasha].[Bakasha_Number] not in (select * from irelevant)
      and ([IB_Entity_Events].[Event_Code] = '1100' or [IB_Entity_Events].[Event_Code] = '1101')
      and  [Table_Number] = '2'
      and [Maslul_Code] = '1'

简洁解决方案 #

用ROW_NUMBER()窗口函数按请求编号分区，给事件码排序（优先1100），再筛选每个分区的第一条记录，逻辑清晰且代码简洁：

WITH 
    -- 排除有1552/1567事件的请求
    excluded_requests as (
        SELECT [Entity_Number] 
        FROM [HFA_M].[dbo].[IB_Entity_Events]
        WHERE Event_Code IN ('1552', '1567')
    ),
    -- 对目标事件按请求编号分区，优先保留1100
    ranked_events as (
        SELECT 
            b.*,
            e.[Entity_Number],
            e.[Event_Code],
            e.[Event_Date],
            -- 按请求编号分区，Event_Code='1100'排第一
            ROW_NUMBER() OVER(PARTITION BY b.[Bakasha_Number] ORDER BY CASE e.[Event_Code] WHEN '1100' THEN 1 ELSE 2 END) AS event_rank
        FROM [HFA_M].[dbo].[IB_Bakasha] b
        JOIN [HFA_M].[dbo].[IB_Entity_Events] e ON b.[Bakasha_Number] = e.[Entity_Number]
        WHERE 
            b.[Bakasha_Number] NOT IN (SELECT * FROM excluded_requests)
            AND e.[Event_Code] IN ('1100', '1101')
            AND b.[Table_Number] = '2'
            AND b.[Maslul_Code] = '1'
    )
-- 取每个请求的第一条记录（即优先1100）
SELECT 
    ROW_NUMBER() OVER(ORDER BY [Tik_Binian] ASC) AS OID,
    [Tik_Binian],
    [Bakasha_Number],
    [Bakasha_Description],
    [Ikari_Area],
    [Y_Diur],
    [Floors_Requested_Number],
    [Basement_Floors],
    [Entity_Number],
    [Event_Code],
    [Event_Date]
FROM ranked_events
WHERE event_rank = 1
ORDER BY [Tik_Binian] ASC;

逻辑说明 #

1. excluded_requests CTE：保留原查询中排除有1552/1567事件的请求的逻辑
2. ranked_events CTE：
   • 关联请求表和事件表，过滤出1100/1101的事件
   • 用PARTITION BY b.[Bakasha_Number]按请求编号分组
   • 用ORDER BY CASE e.[Event_Code] WHEN '1100' THEN 1 ELSE 2 END让1100的记录排在同请求的1101记录前面，分配行号1
3. 最后筛选event_rank = 1的记录，确保同一请求下仅保留优先级最高的1100（若存在），否则保留1101

内容的提问来源于stack exchange，提问作者Tal