如何在 WHERE 子句中有聚合函式？

使用 PostgreSQL 9.3.24

問題

只是簡單的操作，但我不知道如何做最簡單的方法。

available_count只有當 sum of s for same product_idis 0and onwayis 0or時，我才能顯示結果NULL？即130479在這種情況下。我想為每個顯示所有值store_id。

謝謝你。

更新

我嘗試在 WHERE 中不允許使用聚合函式。

select
*

from
j_product_store_availability psa 

where
sum(available_count) = '0'
and onway = '0'

group by
product_id

order by
product_id

輸入。

product_id | store_id | available count | onway 
1          | 1        | 0               | 0
1          | 2        | 0               | 0
1          | 3        | 0               | 0
2          | 1        | 0               | 0
2          | 2        | 0               | 0
2          | 3        | 0               | 0
3          | 1        | 0               | 0
3          | 2        | 0               | 0
3          | 3        | 1               | 0
4          | 1        | 0               | 0
4          | 2        | 0               | 0
4          | 3        | 0               | 1

預期輸出。

product_id | store_id | available count | onway 
1          | 1        | 0               | 0
1          | 2        | 0               | 0
1          | 3        | 0               | 0
2          | 1        | 0               | 0
2          | 2        | 0               | 0
2          | 3        | 0               | 0

WITH cte AS ( SELECT product_id
             FROM j_product_store_availability
             GROUP BY product_id
             HAVING 0 = SUM(available_count)
                AND 0 = SUM(onway) )
SELECT t.*
FROM j_product_store_availability t
JOIN cte ON t.product_id = cte.product_id

當然，只有在 available_count 和 onway 都不能為負的情況下，該解決方案才是正確的。

如果沒有，那麼使用

WITH cte AS ( SELECT product_id
             FROM j_product_store_availability
             GROUP BY product_id
             HAVING 0 = COUNT(NULLIF(available_count, 0))
                AND 0 = COUNT(NULLIF(onway, 0)) )
SELECT t.*
FROM j_product_store_availability t
JOIN cte ON t.product_id = cte.product_id

小提琴

引用自：https://dba.stackexchange.com/questions/251434