PostgreSelect in Select Alias Problem

Question

我有查询。

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1b3a39357a5fe028f57b9ac26d147a1d

SELECT users.id as user_ids,
(SELECT
ARRAY_AGG(DISTINCT CONCAT(user_has_bonuses.bonus_id)) as bonus_ids
FROM user_has_bonuses
WHERE user_has_bonuses.user_id = users.id) as BONUS_IDS,
(SELECT
ARRAY_AGG(DISTINCT CONCAT(bonuses.bonus_id))
FROM bonuses
WHERE bonuses.bonus_id IN (BONUS_IDS)
) AS bonusIds
FROM users;

我得到以下错误：

[42703]错误：列；bonus_ids"；不存在提示：也许你的意思是引用列"；bonus_id"；。

如何正确使用此查询？

Answer 1

我认为您正在寻找横向连接。你的例子太懊悔了，没有真正的意义，但逻辑是：

select u.id, ub.*, b.*
from users u
cross join lateral (
select array_agg(distinct ub.bonus_id) bonus_id
from user_has_bonus ub
where ub.user_id = u.id
) ub
cross join lateral (
select ...
from bonuses b
where b.bonus_id = any(ub.bonus_id)
) b

与使用内联子查询不同，您可以在下一个子查询中引用一个子查询的列，如第二个子查询的where子句所示。

请注意，您希望使用any()而不是in来检查值是否属于数组。