and t.start_date > now()转成Oracle
时间: 2024-02-20 16:56:15 浏览: 15
可以这样转换成 Oracle 的语法:
```
AND t.start_date > SYSDATE
```
其中,`SYSDATE` 是 Oracle 中获取当前日期和时间的函数,等同于 MySQL 中的 `NOW()` 函数。
相关问题
帮我优化下这条sql:SELECT epi.business_id,epi.project_id,epi.product_setmeal_id,count(1) num FROM emp_info ei LEFT JOIN (select epi.* from emp_project_info epi where epi.status = 1 AND epi.is_across = 0 AND epi.start_date = ( SELECT max( start_date ) FROM emp_project_info WHERE STATUS = 1 AND emp_id = epi.emp_id AND project_id = epi.project_id AND start_date <= '2023-05-01 00:30:00.028' and is_across = 0 ) AND epi.begin_date <= '2023-05-01 00:30:00.028' AND (epi.end_date is null or epi.end_date > '2023-05-01 00:30:00.028')) epi ON ei.id = epi.emp_id WHERE ei.status = 1 AND ei.type = 3 AND epi.project_id is not null AND epi.source != 5 GROUP BY epi.business_id,epi.project_id,epi.product_setmeal_id
可以尝试以下优化:
1. 使用 EXISTS 替代子查询。
2. 将子查询中的 WHERE 条件移动到 JOIN 条件中。
3. 将比较运算符 "<=" 和 ">" 换成 BETWEEN。
4. 将 SELECT max(start_date) 改为 MAX(start_date) OVER(PARTITION BY emp_id, project_id)。
优化后的 SQL 如下:
SELECT epi.business_id, epi.project_id, epi.product_setmeal_id, COUNT(1) num
FROM emp_info ei
LEFT JOIN emp_project_info epi ON ei.id = epi.emp_id
WHERE ei.status = 1 AND ei.type = 3 AND epi.project_id IS NOT NULL AND epi.source != 5
AND epi.status = 1 AND epi.is_across = 0
AND epi.begin_date <= '2023-05-01 00:30:00.028'
AND (epi.end_date IS NULL OR epi.end_date > '2023-05-01 00:30:00.028')
AND epi.start_date BETWEEN MAX(epi.start_date) OVER(PARTITION BY epi.emp_id, epi.project_id) AND '2023-05-01 00:30:00.028'
GROUP BY epi.business_id, epi.project_id, epi.product_setmeal_id
这样可以避免子查询的计算,提高查询性能。
优化下这个代码 select sum(auth_amt) sum_auth_amt from auth_cont auth where auth_sts = '1' and exists (select 1 from RPT_DUE_LOAN_ACC_M loan where loan.cif_no = auth.cif_no AND loan.send_flag = '2' AND LOAN.PRD_USERDF_TYPE != '3017' AND LOAN.BANK_ID = 162000 and (loan.cif_no in (select cif_no from RPT_DUE_LOAN_ACC_M a where 1 = 1 AND LOAN.YEAR = '2021' AND LOAN.MONTH = '12' AND LOAN.ACCOUNT_STATUS NOT IN ('0', '2') AND ((LOAN.LOAN_BAL > 0 OR LOAN.IN_INTST > 0 OR LOAN.OUT_INTST > 0 OR LOAN.CMPD_INTST > 0) or substr(LOAN.SETTL_DATE, 0, 6) = '202112') AND LOAN.MANG_BR_NO IN (SELECT BR_NO FROM TBL_ORG_DEPARTMENTS START WITH BR_NO = '162000' CONNECT BY PRIOR BR_NO = UP_ONE) group by cif_no and (loan.cif_no in (select cif_no from RPT_DUE_LOAN_ACC_M a where 1 = 1 AND LOAN.YEAR = '2021' AND LOAN.MONTH = '12' AND LOAN.ACCOUNT_STATUS NOT IN ('0', '2') AND ((LOAN.LOAN_BAL > 0 OR LOAN.IN_INTST > 0 OR LOAN.OUT_INTST > 0 OR LOAN.CMPD_INTST > 0) or substr(LOAN.SETTL_DATE, 0, 6) = '202112') AND LOAN.MANG_BR_NO IN (SELECT BR_NO FROM TBL_ORG_DEPARTMENTS START WITH BR_NO = '162000' CONNECT BY PRIOR BR_NO = UP_ONE) group by cif_no AND LOAN.YEAR = '2021' AND LOAN.MONTH = '12' AND LOAN.ACCOUNT_STATUS NOT IN ('0', '2') AND ((LOAN.LOAN_BAL > 0 OR LOAN.IN_INTST > 0 OR LOAN.OUT_INTST > 0 OR LOAN.CMPD_INTST > 0) or substr(LOAN.SETTL_DATE, 0, 6) = '202112') AND LOAN.MANG_BR_NO IN (SELECT BR_NO FROM TBL_ORG_DEPARTMENTS START WITH BR_NO = '162000' CONNECT BY PRIOR BR_NO = UP_ONE))
答案:SELECT SUM(AUTH_AMT) SUM_AUTH_AMT FROM AUTH_CONT AUTH WHERE AUTH_STS = 1 AND EXISTS(SELECT 1 FROM RPT_DUE_LOAN_ACC_M LOAN WHERE LOAN.CIF_NO = AUTH.CIF_NO AND LOAN.SEND_FLAG = 2 AND LOAN.PRD_USERDF_TYPE != 3017 AND LOAN.BANK_ID = 162000 AND LOAN.CIF_NO IN (SELECT CIF_NO FROM RPT_DUE_LOAN_ACC_M A WHERE LOAN.YEAR = 2021 AND LOAN.MONTH = 12 AND LOAN.ACCOUNT_STATUS NOT IN (0, 2) AND ((LOAN.LOAN_BAL > 0 OR LOAN.IN_INTST > 0 OR LOAN.OUT_INTST > 0 OR LOAN.CMPD_INTST > 0) OR SUBSTR(LOAN.SETTL_DATE, 0, 6) = 202112) AND LOAN.MANG_BR_NO IN (SELECT BR_NO FROM TBL_ORG_DEPARTMENTS START WITH BR_NO = 162000 CONNECT BY PRIOR BR_NO = UP_ONE) GROUP BY CIF_NO) AND LOAN.YEAR = 2021 AND LOAN.MONTH = 12 AND LOAN.ACCOUNT_STATUS NOT IN (0, 2) AND ((LOAN.LOAN_BAL > 0 OR LOAN.IN_INTST > 0 OR LOAN.OUT_INTST > 0 OR LOAN.CMPD_INTST > 0) OR SUBSTR(LOAN.SETTL_DATE, 0, 6) = 202112) AND LOAN.MANG_BR_NO IN (SELECT BR_NO FROM TBL_ORG_DEPARTMENTS START WITH BR_NO = 162000 CONNECT BY PRIOR BR_NO = UP_ONE));
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)