SELECT item，date WHERE date 分类: 技术问答 • 2022-06-02 17:23:49

Question

这是我的sql脚本。我试图拉取所有60天内尚未开具发票的项目。它只返回一行。任何想法为什么？谢谢你的帮助！SELECT item，date WHERE date <sysdate - 60

SELECT oel.ordered_item Part_No, 
     MAX(rca.trx_date) AS "Last Invoice Date" 
FROM oe_order_lines_all oel, 
     ra_customer_trx_all rca 
WHERE rca.trx_date < trunc(sysdate)-60 
GROUP BY oel.ordered_item 

Answer 1

您应该添加条件这一行 ''，这是inner join这里，

SELECT oel.ordered_item Part_No, 
     MAX(rca.trx_date) AS "Last Invoice Date" 
FROM oe_order_lines_all oel, 
     ra_customer_trx_all rca 
WHERE rca.trx_date < trunc(sysdate)-60 --------shoud be a condition connects rca and oel 
GROUP BY oel.ordered_item 

Answer 2

我不知道为什么只返回一行。但是你缺少连接条件。如果使用适当的JOIN语法，这将是显而易见的。

根据您的描述，您想要的逻辑更像是这样的：

SELECT oel.ordered_item as Part_No, 
     MAX(rca.trx_date) AS "Last Invoice Date" 
FROM oe_order_lines_all oel LEFT JOIN 
    ra_customer_trx_all rca 
    ON oel.?? = rca.?? 
GROUP BY oel.ordered_item 
HAVING MAX(rca.trx_date) < trunc(sysdate) - 60 OR MAX(rca.trx_date) IS NULL; 

的??是用于连接表的列。