postgreSQL 关联子查询如何写

Question

问题相关代码，请勿粘贴截图

SELECT *
FROM (SELECT DISTINCT temp.*
      FROM (SELECT *,
                   (SELECT DISTINCT user_id
                    FROM ujpm_member
                    WHERE user_type_id = '项AO' AND project_id = t.id) AS poid,
                   (SELECT DISTINCT user_id
                    FROM ujpm_member
                    WHERE user_type_id = '项BO' AND project_id = t.id) AS toid
            FROM ujpm_project t
            WHERE t.tenant_id = 'webApp') temp
               LEFT JOIN ujpm_department upd ON temp.id = upd.project_id AND upd.tenant_id = 'webApp'
               LEFT JOIN ujpm_member upm ON temp.id = upm.project_id AND upm.tenant_id = 'webApp') TOTAL

运行结果及报错内容

more than one row returned by a subquery used as an expression

我的解答思路和尝试过的方法

我想要达到的结果

他这个子查询加limit 1可以查出来 但是要查多个参数 left join 关联子查询如何写了

Answer 1

这个子查询如果返回多条就会报错。

(SELECT DISTINCT user_id
                    FROM ujpm_member
                    WHERE user_type_id = '项BO' AND project_id = t.id) AS toid