我的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,
相关新闻>>
- 发表评论
-
- 最新评论 更多>>