资源描述：
《ORALCE 应用系统的常用经典实用SQL语句示例》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、1、模糊查询（like*%）SelectA.SALES_ACCOUNT,A.COST_OF_SALES_ACCOUNT,b.inventory_item_idfromMTL_SYSTEM_ITEMS_BAwhereA.SEGMENT1LIKE'12%'andA.ORGANIZATION_ID='103'2、按时间查询表数据：（to_date）selecta.*fromPA_EXPENDITURE_ITEMS_ALLawherea.creation_date=to_date('2011-7-2711:14:09','YYYY-MM-DDHH24:mi:ss')3、多条件查询表数据：（
2、and,or）selecta.*fromPA_EXPENDITURE_ITEMS_ALLawherea.org_id<>175anda.creation_date=to_date('2011-7-2711:14:09','YYYY-MM-DDHH24:mi:ss')ora.creation_date=to_date('2011-7-2711:14:13','YYYY-MM-DDHH24:mi:ss')4、对查询记录排序：(orderby)Selectb.inventory_item_id,b.segment1,b.description,b.organization_idfromm
3、tl_system_items_bbwhereb.organization_id=107--组织号可根据实际选择orderbyb.segment1,b.description5、查表中字段重复记录情况数：（havingcount….groupby）selectcount(*),a.CATEGORY_CONCAT_SEGS--只能选一个可能相重主键，如有其它字段则需为非主键frommtl_categories_vahavingcount(*)>1groupbya.CATEGORY_CONCAT_SEGS6、查某表中某字段重复记录：selectb.inventory_item_id,b
4、.segment1,b.description,b.organization_idfrommtl_system_items_bb,(selecta.description,a.organization_idfrom(selectmsb.description,msb.organization_id,count(0)cntfrommtl_system_items_bmsbgroupbymsb.description,msb.organization_idhavingcount(0)>1)a)c--通过查出重复记录，重组为新表Cwhereb.description=c.descript
5、ionandb.organization_id=c.organization_idandb.organization_id=107–组织号可根据实际选择7、查2关联表中a中有而b中没有的记录（b.*(+)…isnull）selecta.inventory_item_id,b.inventory_item_id,a.segment1,a.organization_idfrommtl_system_items_ba,mtl_item_revisions_bbwherea.inventory_item_id=b.inventory_item_id(+)anda.organization_
6、id=b.organization_id(+)andb.inventory_item_idisnull8、多表多条件组合查询并过滤重复数据（Selectdistinct…）*下例实现查某职位下那些员工未定义费用账户的记录selectdistinctsubstr(hao.NAME,1,4)公司代码,papf.employee_number员工编码,papf.last_name员工姓名,hao.name组织名称,pp.NAME职位,paaf.default_code_comb_id费用帐户编码,gcck.concatenated_segments费用帐户名称fromper_positi
7、onspp,per_all_people_fpapf,per_all_assignments_fpaaf,gl_code_combinations_kfvgcck,hr_all_organization_unitshaowherepaaf.person_id(+)=papf.person_idANDpaaf.primary_flag='Y'andpaaf.position_id=pp.position_id(+)andgcck.code_combination_id(
