主页 / dba / 问题 / 251434
Accepted
genderbee
Asked: 2019-10-19 00:08:49 +0800 CST

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

  • 772

使用 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
postgresql postgresql-9.3

2 个回答

  1. 您必须使用窗口函数，它执行类似于 a 的操作GROUP BY，但输出所有行而不仅仅是组。

    SELECT *
FROM (SELECT *,
             sum(available_count) OVER (PARTITION BY product_id) AS sum_count
      FROM j_product_store_availability psa) AS subq
WHERE onway = 0
  AND sum_count = 0
ORDER BY product_id;
    • 1
  2. Best Answer
    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

    小提琴

    • 1

相关问题