sql - SQL Count 具有多个连接条件的数据

Question

所以这是我的简化结构：公司 <- 用户 <- 订单 <- 订单状态

我需要为每个公司执行多个订单计数。每个计数可以有自己的过滤器order_status。作为回报应该是公司的 id 和多个计数。

这是我尝试过的一个计数，但我不能将它与其他计数结合起来。

SELECT c.id as company_id, COUNT(o.id) as orders_count_total
    FROM companies c
    LEFT JOIN users u
        ON u.belongs_to_company_id = c.id
    LEFT JOIN orders o
        ON
            c.id = o.users_company_id OR
           (
                u.id = o.users_id AND
                u.belongs_to_company_id = c.id AND
                o.users_company_id is NULL
            )
    JOIN orders_orderstatus o_status
        ON o.status_id = o_status.id AND NOT (o_status.slug IN ('slug1', 'slug2'))
GROUP BY company_id

同样在这种情况下，我想念所有没有订单的公司

标签： sqlpostgresql

Answer 1

我对此完全不确定，所以我将手指放在“删除”键上，准备在完全错误的情况下取消这个答案，但是你能做两个左连接，每个条件一个，然后合并吗？

SELECT
  c.id as company_id, COUNT(coalesce (o1.id, o2.id)) as orders_count_total
FROM
  companies c
  LEFT JOIN users u
      ON u.belongs_to_company_id = c.id
  LEFT JOIN orders o1
      ON c.id = o1.users_company_id
  left join orders o2 on
      u.id = o2.users_id AND
      u.belongs_to_company_id = c.id AND
      o2.users_company_id is NULL
  JOIN orders_orderstatus o_status
      ON coalesce (o1.status_id, o2.status_id) = o_status.id AND 
      NOT (o_status.slug IN ('slug1', 'slug2'))
GROUP BY company_id

Count 应该只计算非空结果，所以我认为这会起作用。

如果不是，那么如何在 CTE 中进行相同的合并，然后对订单状态表进行内部连接？

with orders as (
  SELECT
    c.id as company_id, coalesce (o1.id, o2.id) as order_id
  FROM
    companies c
    LEFT JOIN users u
        ON u.belongs_to_company_id = c.id
    LEFT JOIN orders o1
        ON c.id = o1.users_company_id
    left join orders o2 on
        u.id = o2.users_id AND
        u.belongs_to_company_id = c.id AND
        o2.users_company_id is NULL
  where
    o1.id is not null or o2.id is not null
)
select
  o.company_id, count (o.order_id) as orders_count_total
from
  orders o
  JOIN orders_orderstatus o_status on
      o.order_id = o_status.id AND 
      NOT (o_status.slug IN ('slug1', 'slug2'))
GROUP BY company_id

我在连接中使用“OR”条件的体验非常糟糕，除非原始条件是高度选择性的——你的似乎不是，因为你似乎想要两个完全不同的连接。