使用多列的组by和具有子句

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的工作方式时，我是不是遗漏了什么？我知道我可以回避这个问题，但我不明白它为什么会这样。

这就是我得到的

​

​

这是我应该得到的

​

​

Answer 1

经过一些讨论后，试着使用这个

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

你写布尔值的时候要三思。

Answer 2

你似乎不懂布尔逻辑。

如果没有those子句，这些就是您的结果。

A   50  51
B   1   51

使用

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

你问:给我看所有的行，哪些类不是50，哪些费用不是51。

和是重要的。

现在，再看看结果，没有子句，并问自己，你为什么会期待任何东西会被退回？

因为这两行都有一个fee=51，所以不会返回任何数据，因为您要求的行的费用与51不同。

这是一个已知的头脑陷阱，当您用AND累积NOT (或<> !=)时，它来自布尔逻辑。一般来说，你不应该这么做。当您有几个NOT时，您应该用OR、not和和累积它们。