ORACLE 中的数据库查询

斯里吉斯

SELECT 
    BILLS.* 
FROM 
    BATELCO_BILLS_S BILLS 
INNER JOIN 
(
    SELECT 
         BTL_BILL_I_NUMBER, 
         MAX(R_MODIFY_DATE) AS MAXDATETIME 
    FROM 
        BATELCO_BILLS_S  
    GROUP BY 
         BTL_BILL_I_NUMBER
) GROUPEDBILLS
    ON BILLS.BTL_BILL_I_NUMBER = GROUPEDBILLS.BTL_BILL_I_NUMBER AND
       BILLS.R_MODIFY_DATE = GROUPEDBILLS.MAXDATETIME 
WHERE 
    (BILLS.BTL_BILL_I_CPR = :B3 OR BILLS.BTL_BILL_C_CPR = :B3) AND
    (TO_DATE(BTL_BILL_I_DATE) BETWEEN TO_DATE(:B2, 'dd/mm/yyyy') AND
     TO_DATE(:B1, 'dd/mm/yyyy') OR
     TO_DATE(BTL_BILL_C_DATE) BETWEEN TO_DATE(:B2, 'dd/mm/yyyy') AND
     TO_DATE(:B1, 'dd/mm/yyyy'))

请通过避免连接来帮助优化查询，以便需要 GROUP BY。

避免重复 BTL_BILL_I_NUMBER 并获取结果。

蒂姆·比格莱森

您可以使用解析函数重写它，例如ROW_NUMBER：

SELECT *
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY BTL_BILL_I_NUMBER
                           ORDER BY R_MODIFY_DATE DESC) rn
    FROM BATELCO_BILLS_S 
    WHERE 
        (BTL_BILL_I_CPR = :B3 OR BTL_BILL_C_CPR = :B3) AND
        (TO_DATE(BTL_BILL_I_DATE) BETWEEN TO_DATE(:B2, 'dd/mm/yyyy') AND
         TO_DATE(:B1, 'dd/mm/yyyy') OR
         TO_DATE(BTL_BILL_C_DATE) BETWEEN TO_DATE(:B2, 'dd/mm/yyyy') AND
         TO_DATE(:B1, 'dd/mm/yyyy'))
) t
WHERE rn = 1;

我不是 Oracle 语法方面的专家，SELECT *上面的调用可能不起作用。如果没有，那么您可以（并且理所当然地）列出您想要实际选择的列。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。