PostgreSQL中外部应用的等效语法是什么 [英] What is the equivalent Syntax for Outer Apply in PostgreSQL

问题描述

我正在尝试查找与OUTER APPLY从MSSQL到PostgreSQL的等效用法的SQL查询，但似乎很难找到。

Im trying to find a SQL query on the equivalent usage of OUTER APPLY from MSSQL to PostgreSQL but it seems hard to find.

我的MSSQL示例查询是这样的

My MSSQL sample query is like this.

希望有人可以帮助我解决我的问题。提前致谢。

hope someone can help me with my problem. thanks in advance.

SELECT table1.col1, table1.col2, Supp.ID, Supp.Supplier

FROM SIS_PRS table1 

OUTER APPLY (SELECT TOP 1 ID, SupplierName  FROM table2 WHERE table2.ID = table1.SupplierID) AS Supp

推荐答案

这是一个横向联接：

SELECT table1.col1, table1.col2, Supp.ID, Supp.Supplier
FROM SIS_PRS table1 LEFT JOIN LATERAL
     (SELECT ID, SupplierName
      FROM table2
      WHERE table2.ID = table1.SupplierID
      FETCH FIRST 1 ROW ONLY
     ) Supp
     ON true;

但是，您可以在任何一个数据库中使用一个相关的子查询就非常接近：

However, you can come pretty close in either database with just a correlated subquery:

SELECT table1.col1, table1.col2, table1.SupplierID, 
       (SELECT Name
        FROM table2
        WHERE table2.ID = table1.SupplierID
        FETCH FIRST 1 ROW ONLY
       ) as SupplierName
FROM SIS_PRS table1;

还请注意，在两个数据库中，都提取一行而没有 ORDER BY 可疑。

Also note that in both databases, fetching one row with no ORDER BY is suspicious.

这篇关于PostgreSQL中外部应用的等效语法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！