SELECT "T_YFK_ORDER"."ID",
"T_YFK_ORDER"."YFK_MONEY",
"T_YFK"."FCT_NO",
"T_YFK_ORDER"."ORDER_NO",
"T_YFK"."EMP_NO",
T_YFK_ORDER.YFK_MONEY - (select nvl(sum(yfk_money),0) from t_yfk_details where t_yfk_details.id=T_YFK_ORDER.id and
t_yfk_details.fno<>:fno) as max
FROM "T_YFK","T_YFK_ORDER"
WHERE T_YFK.ID=T_YFK_ORDER.ID AND
"T_YFK_ORDER"."ORDER_NO" in (:value_string) and
T_YFK_ORDER.YFK_MONEY - (select nvl(sum(yfk_money),0) from t_yfk_details where t_yfk_details.id=T_YFK_ORDER.id and
t_yfk_details.fno<>:fno)>0 and
红字的部分作为字段会查询一边,作为条件也会查询一遍
有没有办法可以优化这个查询?我看过ORACLE 对函数优化的索引,但是我这个子查询里面带有条件,不知道如何去做。另外有人说把这个红字写成函数,就可以少执行检索
请大家多指教,谢谢~
"T_YFK_ORDER"."YFK_MONEY",
"T_YFK"."FCT_NO",
"T_YFK_ORDER"."ORDER_NO",
"T_YFK"."EMP_NO",
T_YFK_ORDER.YFK_MONEY - (select nvl(sum(yfk_money),0) from t_yfk_details where t_yfk_details.id=T_YFK_ORDER.id and
t_yfk_details.fno<>:fno) as max
FROM "T_YFK","T_YFK_ORDER"
WHERE T_YFK.ID=T_YFK_ORDER.ID AND
"T_YFK_ORDER"."ORDER_NO" in (:value_string) and
T_YFK_ORDER.YFK_MONEY - (select nvl(sum(yfk_money),0) from t_yfk_details where t_yfk_details.id=T_YFK_ORDER.id and
t_yfk_details.fno<>:fno)>0 and
红字的部分作为字段会查询一边,作为条件也会查询一遍
有没有办法可以优化这个查询?我看过ORACLE 对函数优化的索引,但是我这个子查询里面带有条件,不知道如何去做。另外有人说把这个红字写成函数,就可以少执行检索
请大家多指教,谢谢~
from
(
SELECT B.ID, B.YFK_MONEY,A.FCT_NO,B.ORDER_NO,A.EMP_NO, b.YFK_MONEY-c.maxmoney as NewMax
FROM T_YFK a,
inner join T_YFK_ORDER b on A.ID = B.ID and b.id=c.id
inner join
(
select id,nvl(sum(yfk_money), 0) as maxmoney
from t_yfk_details inner join T_YFK_ORDER on t_yfk_details.id = B.id
where t_yfk_details.fno <> :fno
group by id
) c on c.id=a.id
WHERE
B.ORDER_NO in (:value_string)
and ....
)
where NewMax>0
兄弟 语句贴完整点 条件给具体值
SELECT *
FROM (SELECT "T_YFK_ORDER"."ID",
"T_YFK_ORDER"."YFK_MONEY",
"T_YFK"."FCT_NO",
"T_YFK_ORDER"."ORDER_NO",
"T_YFK"."EMP_NO",
(SELECT nvl(SUM(yfk_money), 0)
FROM t_yfk_details
WHERE t_yfk_details.id = T_YFK_ORDER.id AND
t_yfk_details.fno <> :fno) AS MAX
FROM "T_YFK", "T_YFK_ORDER"
WHERE T_YFK.ID = T_YFK_ORDER.ID AND
"T_YFK_ORDER"."ORDER_NO" IN (:value_string) AND)
WHERE YFK_MONEY - MAX > 0