时间戳

首页 > 解决方案 > 将 T-sql 转换为 postgresql

sql - 将 T-sql 转换为 postgresql

问题描述

我有一个在 SQL 服务器上成功运行的 T-sql 代码。

SELECT
(SELECT
    Address
FROM
    customer_address
WHERE
    ProspectID = tm.ProspectID
    AND TYPE = 'RESIDENCE') AS Residence_Address
 FROM
    landing.kp_los_trx_master tm
INNER JOIN landing.kp_los_trx_status ts ON
    tm.ProspectID = ts.ProspectID
LEFT JOIN landing.kp_los_confins_branch cb ON
    tm.BranchID = cb.BranchID
LEFT JOIN landing.kp_los_customer_personal cp ON
    tm.ProspectID = cp.ProspectID
LEFT JOIN landing.kp_los_trx_items ti ON
    tm.ProspectID = ti.ProspectID
LEFT JOIN landing.kp_los_trx_apk ta ON
    tm.ProspectID = ta.ProspectID
LEFT JOIN landing.kp_los_customer_employment cem ON
    tm.ProspectID = cem.ProspectID
LEFT JOIN landing.kp_los_customer_emcon ce ON
    tm.ProspectID = ce.ProspectID

但是，当我想使用我创建的代码将其代码转换为 postgresql 时，如下所示；

SELECT
(SELECT
        a.address 
    FROM
        landing.kp_los_customer_address a 
        inner join landing.kp_los_trx_master b on
        a.ProspectID = b.ProspectID
        where a."Type" = 'RESIDENCE') AS Residence_Address
FROM
    landing.kp_los_trx_master tm
INNER JOIN landing.kp_los_trx_status ts ON
    tm.ProspectID = ts.ProspectID
LEFT JOIN landing.kp_los_confins_branch cb ON
    tm.BranchID = cb.BranchID
LEFT JOIN landing.kp_los_customer_personal cp ON
    tm.ProspectID = cp.ProspectID
LEFT JOIN landing.kp_los_trx_items ti ON
    tm.ProspectID = ti.ProspectID
LEFT JOIN landing.kp_los_trx_apk ta ON
    tm.ProspectID = ta.ProspectID
LEFT JOIN landing.kp_los_customer_employment cem ON
    tm.ProspectID = cem.ProspectID
LEFT JOIN landing.kp_los_customer_emcon ce ON
    tm.ProspectID = ce.ProspectID

结果是出现以下错误：

SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression

我不知道如何解决它的代码，有人有什么想法可以帮助我吗？预先感谢

标签： sqlpostgresqltsql

解决方案

您的原始代码应该在 Postgres 中运行——或者实际上在任何数据库中。

但是，在使用相关子查询时，您应该始终限定所有列引用，以确保它们引用的正是您想要的：

SELECT (SELECT ca.Address
        FROM customer_address ca
        WHERE ca.ProspectID = tm.ProspectID AND
              ca.TYPE = 'RESIDENCE'
       ) AS Residence_Address

限定列引用通常是一个好主意，但更强烈建议使用子查询。

注意：我还建议您避免在列名和表名周围使用双引号，这样您就可以避免在查询中使用双引号。

推荐阅读