***我的Oracle SQL总结！！！

junjun19860702

浏览: 1983555 次
性别:
来自: 哈尔滨

最近访客更多访客>>

submergerock

fw_zhong

无象心

hdxiong

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Oracle
经验+注意

###SQL就是：1)增、删、改、查，2)排序用order by XXX desc(时间排序+数值大小排序+String字母顺序排序)，3)如果想重新分组使用group by，4)最大最小等使用oracle函数，5)判断使用decode，6)子查询，7)连表查询，8)存储过程+触发器+索引+视图。等等操作组合

1.##话费产品及佣金查询前台--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

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

--查询(模糊)：
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语句--统计

--单边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,
sum(t.money) TOTALPRICE,
sum(decode(t.operators_code,'40',t.money,0)*decode(t.state,'0',1,0)) YIDONGPRICE,
sum(decode(t.operators_code,'41',t.money,0)*decode(t.state,'0',1,0)) LIANTONGPRICE, 
sum(decode(t.operators_code,'42',t.money,0)*decode(t.state,'0',1,0)) DIANXINPRICE 
from t_phone_sale t where t.bussiness_id='13000000000';

5.##根据类型+状态，查询移动成功总数

--根据类型+状态，查询移动成功总数
select sum(decode(t.operators_code,'40',1,0)*decode(t.state,'0',1,0)) yidongNum 
from t_phone_sale t--40为移动

6.时间倒序、type_num正序排列结果集

--时间倒序、type_num正序排列结果集
select * from t_commission t order by t.create_date desc,t.type_num desc 

select * from t_commission t order by t.create_date desc,t.type_num asc

7.在线充值，业务查询，增加统计项

--前台:在线充值，业务查询，
--增加统计项（业务总数，成功业务总数，总资金数，成功业务资金数，佣金合计）
--sql为:
select count(*) as recordTotal,nvl(sum(decode(t1.state,'0',1,0)),0) as successTotal,
nvl(sum(t1.money),0) as moneyTotal,
nvl(sum(decode(t1.state,'0',t1.money,0)),0) as successMoneyTotal,
nvl(sum(decode(t1.state,'0',t1.commission,0)),0) as commissionTotal 
from T_PHONE_SALE t1 where 1=1 and t1.state='0' and t1.bussiness_id='15104667195' 
and t1.create_date>=to_date('2011-07-27 11:49','yyyy-MM-dd HH24:MI') 
and t1.create_date<=to_date('2011-08-26 23:59','yyyy-MM-dd HH24:MI');

8.Oracle查询前10条记录

select * from t_product_type where rownum <=10

9.二版产品及佣金查询

SELECT
  t1.PRODUCT_NUM  as productNum ,
  t2.product_name  as productName ,
  t2.roll_back  as rollBack ,                
  t1.TYPE_NUM  as typeNum ,            
  t1.CREATE_DATE  as createDate ,      
  t1.EMPLOYEE_ID  as employeeId ,      
  t1.ALTER_DATE  as alterDate ,
  t1.MONEY  as money ,         
  t1.FAST_COMMISSION  as fastCommission ,      
  t1.NORM_COMMISSION  as normCommission ,              
  t1.OTHER_FAST_COMMISSION  as otherFastCommission ,      
  t1.OTHER_NORM_COMMISSION  as otherNormCommission ,   
  t1.ALL_FAST_COMMISSION  as allFastCommission ,      
  t1.ALL_NORM_COMMISSION  as allNormCommission ,                 
  t1.FAST_PRICE  as fastPrice ,      
  t1.NORM_PRICE  as normPrice ,            
  t1.OTHER_FAST_PRICE  as otherFastPrice ,      
  t1.OTHER_NORM_PRICE  as otherNormPrice ,      
  t1.ALL_FAST_PRICE  as allFastPrice ,      
  t1.ALL_NORM_PRICE  as allNormPrice,
  decode(t2.area_code,'0451',t1.FAST_COMMISSION,decode(t2.province_code,'255',t1.OTHER_FAST_COMMISSION,t1.ALL_FAST_COMMISSION)) as  myCommission,
  decode(t2.area_code,'0451',t1.FAST_PRICE,decode(t2.province_code,'255',t1.OTHER_FAST_PRICE,t1.ALL_FAST_PRICE)) as  myPrice                         
FROM  T_COMMISSION t1,t_product_type t2,T_BUSSINESS_SAFE t3
where 
  t3.type_num=t1.type_num  
  and t1.product_num=t2.product_num and t2.state='10'
  and t2.area_code='0571'

10.显示最晚创建的公告，且只显示一条

select * from (select * from t_upgrade t where t.finish_date>=sysdate order by t.create_date desc) where rownum = 1

11.更新数据，可以用这样查出来

select t.*, t.rowid from t_bussiness_safe t where t.bussiness_id='15145117218' for update

12.查询在发布时间、结束时间之内的升级公告

--publish_date为发布时间，t.finish_date为结束时间
select * from t_upgrade t where 
t.publish_date<=sysdate 
and t.finish_date>=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') 
order by t.create_date desc

合成sqlMap文件(TUpgrade.xml)

<select id="queryModel"  parameterClass="com.hanpeng.base.phone.model.TUpgrade" 
					resultClass="com.hanpeng.base.phone.model.TUpgrade">
	select
		UPGRADE_NUM as upgradeNum ,
		UPGRADE_INFO as upgradeInfo ,
		CREATE_DATE as createDate ,
		PUBLISH_DATE as publishDate ,
		FINISH_DATE as finishDate ,
		EMPLOYEE_ID as employeeId
	from (select * from t_upgrade t where t.publish_date&lt;=sysdate 
	and t.finish_date>=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') 
	order by t.create_date desc) where rownum = 1
</select>

13.oracle随机查询

-- 从表zeeno中“全表扫描”随机抽取10%的记录，随机查询5条记录

select object_name from zeeno sample(10) where rownum<6

14.查询前10条记录

select * from test where rownum <=10