sql - 这可以变得更容易吗？

Question

有没有更简单的方法来做到这一点？我只想要 6 年中至少有 2 年有收费代码的家庭 - (cy=当年 ly = 去年等)??

Exists (SELECT * FROM ac_ViewMultiHouseholdChargeCodeYearCode AS VMH 
WHERE 
aa_Households.HouseholdID = VMH.HouseholdiD and 
VMH.ChargeCode = 'Dinner' and 
(VMH.ChargeCount_CY > 0 and
 (VMH.ChargeCount_LY > 0 or
  VMH.ChargeCount_2Y > 0 or 
  VMH.ChargeCount_3Y > 0 or 
  VMH.ChargeCount_4Y > 0 or 
  VMH.ChargeCount_5Y > 0)) or 
(VMH.ChargeCount_LY > 0 and 
 (VMH.ChargeCount_CY > 0 or
  VMH.ChargeCount_2Y > 0 or
  VMH.ChargeCount_3Y > 0 or 
  VMH.ChargeCount_4Y > 0 or
  VMH.ChargeCount_5Y > 0)) or
(VMH.ChargeCount_2Y > 0 and 
 (VMH.ChargeCount_LY > 0 or
  VMH.ChargeCount_CY > 0 or 
  VMH.ChargeCount_3Y > 0 or
  VMH.ChargeCount_4Y > 0 or
  VMH.ChargeCount_5Y > 0)) or
(VMH.ChargeCount_3Y > 0 and
 (VMH.ChargeCount_LY > 0 or 
  VMH.ChargeCount_2Y > 0 or
  VMH.ChargeCount_CY > 0 or
  VMH.ChargeCount_4Y > 0 or
  VMH.ChargeCount_5Y > 0)) or
(VMH.ChargeCount_4Y > 0 and
 (VMH.ChargeCount_LY > 0 or
  ChargeCount_2Y > 0 or
  VMH.ChargeCount_3Y > 0 or
  VMH.ChargeCount_CY > 0))) 

Answer 1

您可以将其简化为:

where (
  (case when VMH.ChargeCount_CY > 0 then 1 else 0 end) +
  (case when VMH.ChargeCount_LY > 0 then 1 else 0 end) +
  (case when VMH.ChargeCount_2Y > 0 then 1 else 0 end) +
  (case when VMH.ChargeCount_3Y > 0 then 1 else 0 end) +
  (case when VMH.ChargeCount_4Y > 0 then 1 else 0 end) +
  (case when VMH.ChargeCount_5Y > 0 then 1 else 0 end)
) >= 2