模拟VLOOKUP功能的Oracle SQL查询

Question

我正试图在我的Oracle SQL查询中获得VLOOKUP功能。我尝试过在其中使用子查询select语句，但它没有返回任何值。

所需逻辑

VLOOKUP逻辑在Lookup_Link_Date字段中是必需的，然后也在Same_Date字段中使用。

基本上在Excel中，Lookup_Link_Date字段在String字段中查找Link_String字段，然后返回其旁边的date

为了尝试在Oracle SQl中复制这一点，我创建了一个子查询select语句，该语句查找date，其中type = type_ref AND no = no_ref从未返回任何结果，我不知道为什么。

当前查询

SELECT DISTINCT
g.type AS Type, 
g.no AS Number,
g.date AS Date, 
g.type_ref AS Type_Ref,
g.no_ref AS No_Ref, 
g.type||g.no||g.company AS String,
g.type_ref||g.no_ref||g.company AS Link_String,
(SELECT g.date 
 FROM gtable g 
 WHERE g.type_ref = g.type 
 AND g.no_ref = g.no) AS Lookup_Link_Date,
CASE WHEN (SELECT g.date 
           FROM gtable g 
           WHERE g.type_ref = g.type 
           AND g.no_ref = g.no) = g.date 
     THEN 'YES' 
     ELSE 'NO' 
     END AS Same_Date
FROM gtable g

尝试#2查询

SELECT DISTINCT
g.type AS Type, 
g.no AS Number,
g.date AS Date, 
g.type_ref AS Type_Ref,
g.no_ref AS No_Ref, 
g.type||g.no||g.company AS String,
g.type_ref||g.no_ref||g.company AS Link_String,
(SELECT b.date 
 FROM gtable b 
 WHERE b.type_ref = b.type 
 AND b.no_ref = b.no) AS Lookup_Link_Date,
CASE WHEN (SELECT d.date 
           FROM gtable d 
           WHERE d.type_ref = d.type 
           AND d.no_ref = g.no) = d.date 
     THEN 'YES' 
     ELSE 'NO' 
     END AS Same_Date
FROM gtable g

尝试#3工作查询

SELECT DISTINCT
g.type AS Type, 
g.no AS Number,
g.date AS Date, 
g.type_ref AS Type_Ref,
g.no_ref AS No_Ref, 
g.type||g.no||g.company AS String,
g.type_ref||g.no_ref||g.company AS Link_String,
(SELECT date 
 FROM gtable 
 WHERE type_ref = g.type 
 AND no_ref = g.no) AS Lookup_Link_Date,
CASE WHEN (SELECT date 
           FROM gtable 
           WHERE type_ref = g.type 
           AND no_ref = g.no) = g.date 
     THEN 'YES' 
     ELSE 'NO' 
     END AS Same_Date
FROM gtable g

Answer 1

检查您的别名。内外都是g。因此，g.type_ref = g.type和g.no_ref = g.no应该不会比较您希望它们比较的内容。

马特编辑：这是马特为他工作的问题：

SELECT DISTINCT
g.type AS Type, 
g.no AS Number,
g.date AS Date, 
g.type_ref AS Type_Ref,
g.no_ref AS No_Ref, 
g.type||g.no||g.company AS String,
g.type_ref||g.no_ref||g.company AS Link_String,
(SELECT date 
 FROM gtable 
 WHERE type_ref = g.type 
 AND no_ref = g.no) AS Lookup_Link_Date,
CASE WHEN (SELECT date 
           FROM gtable 
           WHERE type_ref = g.type 
           AND no_ref = g.no) = g.date 
     THEN 'YES' 
     ELSE 'NO' 
     END AS Same_Date
FROM gtable g;

EDIT（接受后：-）以下是如何使用自联接编写查询：

SELECT 
  g.type AS Type, 
  g.no AS Number,
  g.date AS Date, 
  g.type_ref AS Type_Ref,
  g.no_ref AS No_Ref, 
  g.type||g.no||g.company AS String,
  g.type_ref||g.no_ref||g.company AS Link_String,
  gref.date AS Lookup_Link_Date,
  CASE WHEN g.date = gref.date THEN 'YES' ELSE 'NO' END AS Same_Date
FROM gtable g
LEFT JOIN gtable gref ON g.type = gref.type_ref and g.no = gref.no_ref;

（我删除了DISTINCT。有重复的吗？然后你需要它，否则就不需要了。）