Sql使用日常总结

• distinct关键字，子查询语句中出现distinct函数，切勿使用distinct，效率很慢，可以用union替代
• 使用exists函数替换distinct函数，过滤重复数据
• selectdistinctu.*
   fromEQUIPMENT_Brand u
   joinBrandEquipmentCategory b
     onu.id = b.brand_id
   joinEQUIPMENT_EquipmentCategory e
     one.id = b.equipmentcategory_id
   wheree.namelike'%服务%';
  
  
  selectu.*
   fromEQUIPMENT_Brand u
   whereexists(select1
           fromBrandEquipmentCategory b
           joinEQUIPMENT_EquipmentCategory e
             onb.equipmentcategory_id = e.id
          whereu.id = b.brand_id
            ande.namelike'%服务%')

• http://www.vaikan.com/what-do-you-know-about-sql-performance/

//一个字段里面的值是"a,b,c",分成三行读取

witht1as

(

    select'a,b,c'c1fromdual

)

selectdistinctregexp_substr(c1,'[^,]+',1,level) c1

fromt1

connectbylevel<=length(c1) -length(replace(c1,',','')) +1

--update并且关联其他表

updateMaintenance_Special t

set(t.accountidofissued, t.departmentidofissued) = (

selecta.id, d.idfromoura_account aleftjoinoura_department d

ona.department_id = d.id

wherea.name = t.accountofissued

)

whereexists

(select1fromMaintenance_Special t

leftjoinoura_account a

ont.accountofissued = a.name

leftjoinoura_department d

ona.department_id = d.id)

--递归查询

select wmsys.wm_concat(t.id) ids from
(select D.id from oura_department D
start with D.Id=14

connect by prior D.id=D.PARENTDEPARTMENT_ID) t

--时间统一占位符

WHERE A.CLEARDT >= TRUNC(?) AND A.CLEARDT < TRUNC(?+1)

--row_number()  OVER(...)使用

简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。