我的Oracle SQL总结！！！

1.##话费产品及佣金查询 前台--SQL
Sql代码 
--select count(*)  
select t2.product_name,t2.money,t1.fast_price,t1.fast_commission,t2.roll_back 
from t_commission t1,t_product_type t2,T_BUSSINESS_SAFE t3  
where 1=1 and t3.type_num=t1.type_num and t1.product_num=t2.product_num 
--下面的参数，需要动态传进来 
and t3.bussiness_id='15145117218' and t2.operators_code='41' and t2.area_code='0451'  
and t2.money in(1,3,5,100,200) 
 
2.##二版冲正状态管理 后台--SQL
Sql代码 
select t.*, t.rowid from t_product_type t 
 
SELECT DISTINCT t1.province_code,t2.province,t3.d_name, 
t1.operators_code,t1.roll_back  
FROM t_product_type t1,t_area t2,t_dictionary t3 
where t1.province_code=t2.province_code and t1.operators_code=t3.d_code  
order by t1.province_code asc,t1.operators_code asc 
 
--------------------------------------------------------------------------------------- 
--select count(*) from() 如果想查下面结果集的个数，只需将下面的sql套在from的括号中 
select tpt.province_code as provinceCode,max(ta.province) as proviceName, 
max(decode(tpt.operators_code,'40','移动','')) as  opn1, 
max(decode(tpt.operators_code,'40','40','')) as  op1, 
max(decode(tpt.operators_code,'40',tpt.roll_back,'')) as rb1, 
max(decode(tpt.operators_code,'41','联通','')) as opn2, 
max(decode(tpt.operators_code,'41','41','')) as  op2, 
max(decode(tpt.operators_code,'41',tpt.roll_back,'')) as rb2, 
max(decode(tpt.operators_code,'42','电信','')) as opn3, 
max(decode(tpt.operators_code,'42','42','')) as  op3, 
max(decode(tpt.operators_code,'42',tpt.roll_back,'')) as rb3 
from t_product_type tpt left join t_area ta on tpt.province_code=ta.province_code  
group by tpt.province_code  
--可以加having限制条件 
--having 1=1 and tpt.province_code='251' 
 
--更新 
update t_product_type t set t.roll_back='10' where t.province_code='251' 
and t.operators_code='40' 
 
3.##短信套餐查询统计 后台--sql
Sql代码 
--查询(模糊)： 
select bi.bussiness_id,bi.bussiness_name,vsc.*,dic.d_server_name from 
t_bussiness_combo bc left join t_bussiness_info bi on bc.bussiness_id=bi.bussiness_id 
left join t_value_service_combo vsc on bc.combo_num = vsc.combo_num 
left join t_dictionary dic on dic.d_code = vsc.state where 1=1; 
---------------------------------------------------------------------------------------------------------------- 
--详细： 
select t2.bussiness_id,t3.bussiness_name,t1.combo_name,t1.combo_info, 
t1.combo_duration,t1.combo_money, dic.d_server_name from  
t_value_service_combo t1,t_bussiness_combo_record t2,T_BUSSINESS_INFO t3,t_dictionary dic  
where t1.combo_num=t2.combo_num and t2.bussiness_id=t3.bussiness_id  
and t2.record_type=dic.d_code and t2.bussiness_id='13000000000'; 
 
4.##单边sql语句--统计
Java代码 
--单边sql语句--统计 
select sum(1) TOTALNUM, 
sum(decode(t.operators_code,'40',1,0)*decode(t.state,'0',1,0)) YIDONGNUM, 
sum(decode(t.operators_code,'41',1,0)*decode(t.state,'0',1,0)) LIANTONGNUM, 
sum(decode(t.operators_code,'42',1,0)*decode(t.state,'0',1,0)) DIANXINNUM,