查询以按关联对象的数量选择

debugcn 发表于 Dev

奥兰多

我有两个看起来像下面的表：

Orders
------
id
tracking_number

ShippingLogs
------
tracking_number
created_at
stage

我想选择仅与一个关联的ShippingLog的订单ID，并且ShippingLog的阶段必须为error。如果它有两个ShippingLog条目，则不需要。如果它有一个ShippingLog错误，则其阶段为shipped，我不想要它。

这就是我所拥有的，并且不起作用，而且我知道为什么（它找到带有错误的日志，但是无法知道是否还有其他错误）。我只是真的不知道如何以我需要的方式获得它。

SELECT DISTINCT 
    orders.id, shipping_logs.created_at, COUNT(shipping_logs.*)
FROM
    orders
JOIN
    shipping_logs ON orders.tracking_number = shipping_logs.tracking_number
WHERE
    shipping_logs.created_at BETWEEN '2021-01-01 23:40:00'::timestamp AND '2021-01-26 23:40:00'::timestamp AND shipping_logs.stage = 'error'

GROUP BY
    orders.id, shipping_logs.created_at
HAVING
    COUNT(shipping_logs.*) = 1
ORDER BY
    orders.id, shipping_logs.created_at DESC;

蒂姆·比格莱森（Tim Biegeleisen）

如果您想保留给定要求的两个表的连接中的每一列，那么我建议COUNT在这里使用它作为解析函数：

WITH cte AS (
    SELECT o.id, sl.created_at,
           COUNT(*) OVER (PARTITION BY o.id) num_logs,
           COUNT(*) FILTER (WHERE sl.stage <> 'error')
                    OVER (PARTITION BY o.id) non_error_cnt
    FROM orders o
    INNER JOIN shipping_logs sl ON sl.tracking_number = o.tracking_number
    WHERE sl.created_at BETWEEN '2021-01-01 23:40:00'::timestamp AND
                                '2021-01-26 23:40:00'::timestamp
)

SELECT id AS order_id, created_at
FROM cte
WHERE num_logs = 1 AND non_error_cnt = 0
ORDER BY id, created_at DESC;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。