Postgresql
如何在 WHERE 子句中有聚合函式?
使用 PostgreSQL 9.3.24
問題
只是簡單的操作,但我不知道如何做最簡單的方法。
available_count
只有當 sum of s for sameproduct_id
is0
andonway
is0
or時,我才能顯示結果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