PostgreSQL按子查询输出筛选条件

6za6bjd0 于 2022-12-12 发布在 PostgreSQL

关注(0)|答案(1)|浏览(184)

我有一个带有子查询的查询，结果输出为yesterday_sum列。我只需要筛选其中包含yesterday_sum > 1的行，但不能添加HAVING sum(p.profit_percent) / :workDays > 1 AND yesterday_sum > 1，因为yesterday_sum不是GROUP BY的一部分。我不能为positions p添加条件，因为yesterday_sum不是positions列。

SELECT u.id                              AS id,
       u.nickname                        AS title,
       sum(p.profit_percent) / :workDays AS middle,
       (
           SELECT sum(ps.profit_percent)
           FROM positions ps
           WHERE ps.user_id = u.id
             AND ps.open_at BETWEEN
               :dateYesterday AND
               :dateYesterday + INTERVAL '1 day'
           GROUP BY (ps.user_id)
       )                                 AS yesterday_sum
FROM positions p
         INNER JOIN users u ON u.id = p.user_id
    AND p.profit_percent IS NOT NULL
    AND p.parent_ticket IS NULL
    AND p.close_at IS NOT NULL
    AND p.open_at BETWEEN :dateFrom AND :dateTo
GROUP BY (u.id, u.nickname)
HAVING sum(p.profit_percent) / :workDays > 1
ORDER BY middle DESC;

如何删除yesterday_sum列小于1且为NULL的行？

postgresql

来源：https://stackoverflow.com/questions/74752063/postgresql-filter-condition-by-subquery-output

1条答案

按热度按时间

htzpubme1#

要在WHERE子句中使用列yesterday_sum，可以通过将查询作为主查询的子查询来生成命名列。然后，过滤就变得很简单了。
例如，您可以：

select *
from (
  SELECT u.id                              AS id,
       u.nickname                        AS title,
       sum(p.profit_percent) / :workDays AS middle,
       (
           SELECT sum(ps.profit_percent)
           FROM positions ps
           WHERE ps.user_id = u.id
             AND ps.open_at BETWEEN
               :dateYesterday AND
               :dateYesterday + INTERVAL '1 day'
           GROUP BY (ps.user_id)
       )                                 AS yesterday_sum
  FROM positions p
         INNER JOIN users u ON u.id = p.user_id
    AND p.profit_percent IS NOT NULL
    AND p.parent_ticket IS NULL
    AND p.close_at IS NOT NULL
    AND p.open_at BETWEEN :dateFrom AND :dateTo
  GROUP BY (u.id, u.nickname)
  HAVING sum(p.profit_percent) / :workDays > 1
) x
where yesterday_sum >= 1  -- yesterday_sum is available here
ORDER BY middle DESC;

赞(0）回复(0）举报 2022-12-12

我来回答

PostgreSQL按子查询输出筛选条件

1条答案

相关问题

热门标签

最新问答