oracle，如何在连接子查询中使用外部表列

jdzmm42g 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(106)

给予下面的表格我需要从NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008中获取所有记录，并为每行从NAT_DASH_AUD_ELEMENTO_WORKFLOW_007中获取，

AuditData <= NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008.AuditData
and ID_ELEMENTO_008 = ID_007

使用join不能做到这一点，因为代码将是，并且不允许内部条件

select * from 

NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008
INNER JOIN
  (SELECT * FROM NAT_DASH_AUD_ELEMENTO_WORKFLOW_007
  WHERE 
    NAT_DASH_AUD_ELEMENTO_WORKFLOW_007.AuditData <= NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008.AuditData 
    AND ID_007 = ID_ELEMENTO_008
  ORDER BY NAT_DASH_AUD_ELEMENTO_WORKFLOW_007."AuditData" DESC
  fetch next 1 rows only)
ON ID_007 = ID_ELEMENTO_008

与子查询在选择我被迫选择一列只有
有没有一种SQL标准方法可以做到这一点，而不会因为简单需求而混淆对象或记录？
表格：

CREATE TABLE "NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008" 
   (    "AuditId" NUMBER(9,0),
    "AuditAction" VARCHAR2(250 BYTE) ,
    "AuditUser" VARCHAR2(250 BYTE) ,
    "AuditData" TIMESTAMP (6), 
    "ID_008" NUMBER(12,0), 
    "ID_LINGUA_008" NUMBER(9,0), 
    "ID_ELEMENTO_008" NUMBER(9,0), 
    "NOTE_008" VARCHAR2(4000 CHAR)
)

和

CREATE TABLE "NAT_DASH_AUD_ELEMENTO_WORKFLOW_007" 
(   "AuditId" NUMBER(9,0) ,
    "AuditAction" VARCHAR2(250 BYTE) ,
    "AuditUser" VARCHAR2(250 BYTE),
    "AuditData" TIMESTAMP (6), 
    "ID_007" NUMBER(12,0), 
    "ID_STATO_ORIGINE_007" NUMBER(9,0), 
    "ID_STATO_DESTINAZIONE_007" NUMBER(9,0), 
    "NOTA_OBBLIGATORIA_007" NUMBER(1,0), 
    "ALLEGATO_OBBLIGATORIO_007" NUMBER(1,0), 
    "FINALE_007" NUMBER(1,0), 
    "ID_WORKFLOW_007" NUMBER(9,1)
)

oracle

来源：https://stackoverflow.com/questions/76998749/oracle-how-to-use-outer-table-column-in-joined-subquery

2条答案

按热度按时间

wwwo4jvm1#

在Oracle 12中，您可以使用LATERAL连接：

SELECT *
FROM   NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008 n8
       CROSS JOIN LATERAL (
         SELECT *
         FROM   NAT_DASH_AUD_ELEMENTO_WORKFLOW_007 n7
         WHERE  n7."AuditData" <= n8."AuditData"
         AND    n7.ID_007      =  n8.ID_ELEMENTO_008
         ORDER BY n7."AuditData" DESC
         FETCH FIRST ROW ONLY
       )

或者，在早期版本中，使用ROW_NUMBER解析函数：

SELECT *
FROM   (
  SELECT n7.*,
         n8.*,
         ROW_NUMBER() OVER (
           PARTITION BY n7.ID_007
           ORDER     BY n7."AuditData" DESC
         ) AS rn
  FROM   NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008 n8
         INNER JOIN NAT_DASH_AUD_ELEMENTO_WORKFLOW_007 n7
         ON     n7."AuditData" <= n8."AuditData"
            AND n7.ID_007      =  n8.ID_ELEMENTO_008
)
WHERE  rn = 1;

赞(0）回复(0）举报 2023-10-16

kyvafyod2#

你可以检查这个查询：

SELECT * 
FROM NAT_DASH_AUD_ELEMENTO_WORKFLOW_LOC_008 n8
INNER JOIN NAT_DASH_AUD_ELEMENTO_WORKFLOW_007 n7
ON n7.ID_007 = n8.ID_ELEMENTO_008
    AND n7.AuditData <= n8.AuditData
ORDER BY n7.ID_007, n7.AuditData DESC, n8.AuditData;  --ORDER BY...

赞(0）回复(0）举报 2023-10-16

我来回答

oracle，如何在连接子查询中使用外部表列

2条答案

相关问题

热门标签

最新问答