sql优化问题

[骑猪去东莞]

select * from (Select count(*) as count1 from edr_cont_hold_release Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=1 )
union ( Select count(*) as count1 from edr_cont_hold_release Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=2 and ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >1)
union ( Select count(*) as count1 from edr_cont_hold_release Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=3 and ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >2)
union ( Select count(*) as count1 from edr_cont_hold_release Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >3 )
这个sql语句如何优化哇，请高手作答，谢谢

[dawugui]

建议你提供详细的资料：
例如表的结构，表之间的关系，测试数据，相关算法及需要的结果。
这样有助于我们理解你的意思，更主要的是能尽快让你获得答案或解决问题的方法。

[geniuswjt]

/*
1.请用sql代码发
2.排版请美观

[NBDBA]

TO_NUMBER(release_datetime - cont_in_tml_datetime))
这是个自定义函数？

[dawugui]

你这个是四个不同条件的数量,没什么可优化的,不过最外面一层的嵌套没什么用处.
直接这样即可.

Select count(*) as count1 from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=1 
union 
Select count(*) as count1 from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=2 and ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >1
union
Select count(*) as count1 from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=3 and ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >2
union
Select count(*) as count1 from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >3 

[骑猪去东莞]

[Quote=引用 3 楼 nbdba 的回复:]
TO_NUMBER(release_datetime - cont_in_tml_datetime))
这是个自定义函数？
[/Quote]
这个不是，是内置函数

[NBDBA]

把那些函数想法去掉，应该有代替方法的

[骑猪去东莞]

[Quote=引用 5 楼 xiewenping 的回复:]
引用 3 楼 nbdba 的回复:
TO_NUMBER(release_datetime - cont_in_tml_datetime))
这是个自定义函数？

这个不是，是内置函数
[/Quote]oracle的内置

[骑猪去东莞]

[Quote=引用 4 楼 dawugui 的回复:]
你这个是四个不同条件的数量,没什么可优化的,不过最外面一层的嵌套没什么用处.
直接这样即可.

SQL code

Select count(*) as count1 from edr_cont_hold_release
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(rel……
[/Quote]为什么它查处的结果会自动排序啊，我要是它不自动排序怎么弄哇

[geniuswjt]

=号左边不要用公式

[骑猪去东莞]

[Quote=引用 9 楼 geniuswjt 的回复:]
=号左边不要用公式
[/Quote]
？，不明白你说的意思

[dawugui]

[Quote=引用 8 楼 xiewenping 的回复:]
为什么它查处的结果会自动排序啊，我要是它不自动排序怎么弄哇[/Quote]
建议把union 换成union all,如果你不想排序,可加个参数,然后order by
以下两段,自己试试哪段是正确的.

Select count(*) as count1 , 1 px from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=1 
union 
Select count(*) as count1 , 2 px from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=2 and ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >1
union
Select count(*) as count1 , 3 px from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=3 and ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >2
union
Select count(*) as count1 , 4 px from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >3 
order by px

或者

select * from 
(
Select count(*) as count1 , 1 px from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=1 
union 
Select count(*) as count1 , 2 px from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=2 and ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >1
union
Select count(*) as count1 , 3 px from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) <=3 and ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >2
union
Select count(*) as count1 , 4 px from edr_cont_hold_release 
Where cont_status in ('IF','EM') And HOLD_CONDITION = 'DT' And ROUND(TO_NUMBER(release_datetime - cont_in_tml_datetime)) >3 
) t
order by px

[骑猪去东莞]

[Quote=引用 11 楼 dawugui 的回复:]
引用 8 楼 xiewenping 的回复:
为什么它查处的结果会自动排序啊，我要是它不自动排序怎么弄哇
建议把union 换成union all,如果你不想排序,可加个参数,然后order by
以下两段,自己试试哪段是正确的.


SQL code

Select count(*) as count1 , 1 px from edr_cont_hold_release
W……
[/Quote]
谢了，分就给你把