将相关子查询限制为只有一条记录

Question

我试图使用相关子查询，但我试图将其限制在“最佳”记录。当我使用SQL时，每个BigTable.identifier有两行，我希望只有一行。在“UNION”语句中，后半部分比前半部分更可取。然而，有时会需要前半部分。有什么想法吗？代码如下：

select 
  BigTable.identifier,
  Correlated.ID,
  Correlated.Effective_Date,
  Correlated.Period_Number

from
  BigTable

inner join

( 
select
  TOP 2147483647
  Table3.identifier,
  Table4.Effective_Date,
  Table4.Period_Number

from
  Table3
inner join Table4 on Table3.matching_key = Table4.matching_key
where
  Table4.Period_Number = 0
order by Table4.Effective_Date desc

UNION

 select 
  TOP 2147483647
  Table3.Identifer,
  Table4.Effective_Date,
  Table4.Period_Number
 from
   Table3
 inner join Table5 on Table3.matching-key = Table5.matching-key
 inner join Table4 on Table5.key1 = Table4.key1 and
    Table5.key2 = Table4.key2
where
   Table4.period_number = 1 
order by Table4.Effective_Date desc
) as Correlated 

on BigTable.identifier = Correlated.identifier

Answer 1

如果UNION中的每个子查询都有一些条件，如果该行是不太优先的，则排除该行，那么在UNION中永远不会看到不太优先的行。因此，如果每个都有一个NOT EXISTS (...在联合的另一端有更好的行...)，您将在根上消除不太受欢迎的行。

我不清楚您希望如何使用生效日期。假设您的意思是您更喜欢Period=1，但如果生效日期较短，则您更喜欢Period=0，那么这样的代码可能会起作用。

select 
  BigTable.identifier,
  Correlated.ID,
  Correlated.Effective_Date,
  Correlated.Period_Number

from
  BigTable

inner join

( 
select
  TOP 2147483647
  Table3.identifier,
  Table4.Effective_Date,
  Table4.Period_Number

from
  Table3
inner join Table4 on Table3.matching_key = Table4.matching_key
where
  Table4.Period_Number = 0
 AND NOT EXISTS 
   (select 1 
    from Table5 T5 inner join Table4 T4
    on T5.key1 = T4.key1 and T5.key2 = T4.key2 
    where Table3.matching-key = T5.matching-key
      and (T4.Effective_Date >= Table4.Effective_Date  and T4.Period_Number = 1)
   )
order by Table4.Effective_Date desc

UNION

 select 
  TOP 2147483647
  Table3.Identifer,
  Table4.Effective_Date,
  Table4.Period_Number
 from
   Table3
 inner join Table5 on Table3.matching-key = Table5.matching-key
 inner join Table4 on Table5.key1 = Table4.key1 and
    Table5.key2 = Table4.key2
where
   Table4.period_number = 1 
 AND NOT EXISTS 
   (select 1 
    from Table4 T4
    where Table3.matching-key = T4.matching-key
      and (T4.Period_Number > 0)
      and (T4.Effective_Date > Table4.Effective_Date  and T4.Period_Number = 0)
   )
order by Table4.Effective_Date desc
) as Correlated 

on BigTable.identifier = Correlated.identifier