sql-server - 使用多列的 Group by 和 Have 子句

Question

AND以下子句中的条件的行为Having就像它过滤行一样，即使在它应该是逻辑AND行为时满足其中一个条件也是如此。

with cte as 
( select 'A' as name , 10 as Classes , 11 as Fees union all
 select 'A' as name , 10 as Classes , 10 as Fees union all
  select 'A' as name , 10 as Classes , 10 as Fees union all
   select 'A' as name , 10 as Classes , 10 as Fees union all
    select 'A' as name , 10 as Classes , 10 as Fees union all
     select 'B' as name , 0 as Classes , 10 as Fees union all
      select 'B' as name , 0 as Classes , 10 as Fees union all
       select 'B' as name , 1 as Classes , 10 as Fees union all
        select 'B' as name , -10 as Classes , 10 as Fees union all
         select 'B' as name , 10 as Classes , 11 as Fees 
       )
       -- A has 50 rooms and 51 $ -- so it should not be returned
       -- B has 1 room and 51 $ and should be fetched
        -- why would the AND in the having clause  behave like a row level filter on only the indivigual column 
        -- instead of the combination
        -- The weird thing is the logic behaves as expected when using = but fails when using <>
       select name , sum(Classes) , sUM(Fees)
       from cte 
       group by name 
       having ( SUM(Classes) !=50) AND (sum(Fees) !=51  )

这是奇怪的部分，如果您将 != 替换为如下所示的 =，那么它可以正常工作

with cte as 
( select 'A' as name , 10 as Classes , 11 as Fees union all
 select 'A' as name , 10 as Classes , 10 as Fees union all
  select 'A' as name , 10 as Classes , 10 as Fees union all
   select 'A' as name , 10 as Classes , 10 as Fees union all
    select 'A' as name , 10 as Classes , 10 as Fees union all
     select 'B' as name , 0 as Classes , 10 as Fees union all
      select 'B' as name , 0 as Classes , 10 as Fees union all
       select 'B' as name , 1 as Classes , 10 as Fees union all
        select 'B' as name , -10 as Classes , 10 as Fees union all
         select 'B' as name , 10 as Classes , 11 as Fees 
       )
       -- A has 50 rooms and 51 $ -- so it should not be returned
       -- B has 1 room and 51 $ and should be fetched
        -- why would the AND in the having clause  behave like a row level filter on only the indivigual column 
        -- instead of the combination
        -- The weird thing is the logic behaves as expected when using = but fails when using <>
       select name , sum(Classes) , sUM(Fees)
       from cte 
       group by name 
       having ( SUM(Classes) =1) AND (sum(Fees) =51  )

当谈到工作方式时，我是否遗漏了什么Group by？我知道我可以解决这个问题，但我不明白为什么它会这样。

这就是我得到的

这是我应该得到的

标签： sql-servertsqlhaving

Answer 1

经过一番讨论，尝试使用这个

 having not(SUM(Classes) =50 AND sum(Fees) =51)

和 OP，当你写布尔值时，你必须三思而后行。