我正在尝试统计每个具有多个预订地点id的唯一订单号

Question

我的代码有什么问题？我在执行这些行时从Redshift得到一个错误。

SELECT
COUNT (
DISTINCT (
CASE
WHEN EXISTS(
(
select
1
from
(
select
ci.order_no,
count(ci.booking_location_id) as cnt
from
oms_core_inventory_booking_detail ci
group by
ci.order_no
) cibd
where cibd.cnt > 1
and co.order_no = cibd.order_no
)
) THEN co.order_no
else NULL
END
)
) AS "SP受注数"
from
table co

我收到了一个类似的错误

由于内部错误，不支持这种类型的相关子查询模式

感谢您的帮助。

Saleh

Answer 1

您是否正在查找具有多个位置id的订单id的计数？

SELECT COUNT(*) as order_id_cnt FROM (
SELECT ci.order_no,
COUNT(ci.booking_location_id) AS cnt
FROM oms_core_inventory_booking_detail ci
GROUP BY ci.order_no
HAVING COUNT(ci.booking_location_id)>1
)

建议，

1. 请在发布前格式化代码
2. 问题还不清楚，发布了几行具有代表性的数据集，以及预期的输出是什么