Oracle查询基础（oracle基本查询语句）

这篇文章的内容主要是Oracle基础查询的内容，以及一些常用的查询技巧，比如开窗函数的应用。

--(1)全表查询

--(2)查询表中固定列

--(3)左连接查询 left join 表名 on 关联条件

--(4)where条件查询

--(5)逻辑符 and or

--(6)nvl()函数 select nvl(CTRANSPORTTYPEID,'测试nvl用法') from so_saleorder nvl(dr,0) = 0

--(7)内连接

--(8)去除重复项

--(9)对查询结果进行排序

--(10)分组查询

--(11)开窗

--(12)count() 以及count()开窗

--(13)常数查询

--(14)dual表

--查询销售订单主表 主表主键 csaleorderid

select csaleorderid,vbillcode from so_saleorder where csaleorderid = '10011A10000000021LEC'

--查询销售订单子表 主表主键 csaleorderid

select csaleorderid,csaleorderbid,cmaterialid from so_saleorder_b where csaleorderid = '10011A10000000021LEC'

--1.NC中同一张表主表和子表的连接关系(关联字段) 主表表名.主表主键 = 子表表名.子表中存主表主键值的字段名

--2.多张表查询 左连接 右连接 内连接 全连接

--(1)左连接：左表为基础 关键字 left join ... on

select a.vbillcode as 单据号 from

so_saleorder a

left join so_saleorder_b b on a.csaleorderid = b.csaleorderid

--左连接查询销售订单业务员姓名

--（1）查询销售订单业务员

select cemployeeid from so_saleorder --查询结果分析：销售订单主表中，CEMPLOYEEID字段存的是业务员（人员基本信息）主键值

--（2）左连接关联销售订单主表和人员基本信息表

select a.vbillcode as 单据号,b.name as 业务员

from so_saleorder a

left join bd_psndoc b

on a.CEMPLOYEEID = b.pk_psndoc

--查询销售订单子表中对应的物料编码

select cmaterialvid from so_saleorder_b

select a.cmaterialvid ,b.code as 物料编码,b.name as 物料名称

from so_saleorder_b a

left join bd_material b

on a.cmaterialvid=b.pk_material

--查询销售订单主表单据号以及对应子表的物料信息

select a.VBILLCODE as 单据号,c.code as 物料编码, c.name as 物料名称,d.name as 业务员

from SO_SALEORDER a

left join SO_SALEORDER_B b

on a.csaleorderid=b.csaleorderid

left join BD_MATERIAL c

on b.cmaterialvid=c.pk_material

left join bd_psndoc d

on a.CEMPLOYEEID=d. pk_psndoc

--NC中做报表，必须要有的两个字段：pk_group pk_org

select a.VBILLCODE as vbillcode,c.code as matcode, c.name as matname,d.name as psnname,a.pk_group as pk_group,a.pk_org as pk_org

from SO_SALEORDER a

left join SO_SALEORDER_B b

on a.csaleorderid=b.csaleorderid

left join BD_MATERIAL c

on b.cmaterialvid=c.pk_material

left join bd_psndoc d

on a.CEMPLOYEEID=d. pk_psndoc

--条件查询 where关键字 位于from关键字之后 A1-619717

select a.VBILLCODE as 单据号,c.code as 物料编码, c.name as 物料名称,d.name as 业务员

from SO_SALEORDER a

left join SO_SALEORDER_B b

on a.csaleorderid=b.csaleorderid

left join BD_MATERIAL c

on b.cmaterialvid=c.pk_material

left join bd_psndoc d

on a.CEMPLOYEEID=d. pk_psndoc

where c.code = 'A1-619717' and d.name = '杨建军'

--逻辑符 and or

select a.VBILLCODE as 单据号,c.code as 物料编码, c.name as 物料名称,d.name as 业务员

from SO_SALEORDER a

left join SO_SALEORDER_B b

on a.csaleorderid=b.csaleorderid

left join BD_MATERIAL c

on b.cmaterialvid=c.pk_material

left join bd_psndoc d

on a.CEMPLOYEEID=d. pk_psndoc

where c.code = 'A1-619717' or d.name = '杨建军'

--NC中的逻辑删除 dr = 1 视为删除

select vbillcode from so_saleorder where dr = 1

select vbillcode from so_saleorder where nvl(dr,0) = 0

--以左表为基础

select b.name as 业务员, a.vbillcode as 单据号

from bd_psndoc b

left join so_saleorder a

on a.CEMPLOYEEID = b.pk_psndoc

select b.name as 业务员, a.vbillcode as 单据号

from bd_psndoc b

right join so_saleorder a

on a.CEMPLOYEEID = b.pk_psndoc

--SO20190324000009 SO201903240006 SO201903240000008

--内连接 笛卡尔积 两边必须同时有才能被查出来

select a.vbillcode,b.name

from so_saleorder a,bd_psndoc b

where a.CEMPLOYEEID = b.pk_psndoc

--全连接 full join

--物料和安全库存

--去除重复项 关键字 distinct

select distinct b.name

from so_saleorder a left join bd_psndoc b

on a.CEMPLOYEEID = b.pk_psndoc

--对查询结果进行排序 order by 关键字 asc 升序 desc 降序 order by 字段名 asc/desc

select a.vbillcode ,b.name

from so_saleorder a left join bd_psndoc b

on a.CEMPLOYEEID = b.pk_psndoc

where nvl(a.dr,0) = 0

order by 1 desc,2 desc

--分组查询 关键字 group by 分组查询是和统计函数一起使用

select a.vbillcode as 单据号,c.name ,sum(b.norigtaxmny),avg(b.norigtaxmny)

from

so_saleorder a

left join so_saleorder_b b on a.csaleorderid = b.csaleorderid

left join bd_psndoc c on a.CEMPLOYEEID = c.pk_psndoc

where nvl(a.dr,0) = 0

group by a.vbillcode,c.name

order by 1

--开窗函数

create or replace view v_cost

as

select '0101' as date1, 100 as price from dual

union all

select '0102' as date1, 200 as price from dual

union all

select '0103' as date1, 300 as price from dual

select date1,price,sum(price) over(order by date1) from v_cost

--count()求数据条数总和

select count(*) from so_saleorder where nvl(dr,0) = 0

--每个销售订单表体有多少条数据

select distinct a.vbillcode as 单据号,c.name ,count(*) over(partition by a.vbillcode)

from

so_saleorder a

left join so_saleorder_b b on a.csaleorderid = b.csaleorderid

left join bd_psndoc c on a.CEMPLOYEEID = c.pk_psndoc

where nvl(a.dr,0) = 0

order by 1

--生成序号 rownum

select distinct a.vbillcode as 单据号,c.name ,count(*) over(partition by a.vbillcode),rownum

from

so_saleorder a

left join so_saleorder_b b on a.csaleorderid = b.csaleorderid

left join bd_psndoc c on a.CEMPLOYEEID = c.pk_psndoc

where nvl(a.dr,0) = 0

order by rownum

--rank 排序

select * from (

select distinct a.vbillcode as 单据号,c.name ,count(*) over(partition by a.vbillcode),rownum as rn,rank() over(order by a.vbillcode) as rk

from

so_saleorder a

left join so_saleorder_b b on a.csaleorderid = b.csaleorderid

left join bd_psndoc c on a.CEMPLOYEEID = c.pk_psndoc

where nvl(a.dr,0) = 0

order by rownum

)

order by rk,rn

select * from (

select distinct a.vbillcode as 单据号,c.name ,count(*) over(partition by a.vbillcode),rownum as rn,rank() over(

partition by a.vbillcode order by b.cmaterialid) as rk

from

so_saleorder a

left join so_saleorder_b b on a.csaleorderid = b.csaleorderid

left join bd_psndoc c on a.CEMPLOYEEID = c.pk_psndoc

where nvl(a.dr,0) = 0

order by rownum

)

order by 1,rk

--union all 关键字 列数量一致，每一列数据类型对应

select vbillcode from so_saleorder

union all

select vbillcode from po_order

union all

select vbillcode from mm_mo

select distinct x.pk_material,y.pk_org,y.pk_group,y.code,y.name,z.pk_materialstock from (

select distinct cinventoryvid as pk_material from to_bill_b where nvl(dr,0) = 0

union all

select distinct cbmaterialvid as pk_material from mm_wr_product

where mm_wr_product.pk_org <> mm_wr_product.vbdef7 and nvl(dr,0) = 0

union all

select distinct F.cmaterialvid as pk_material from ic_saleout_h E

left join

ic_saleout_b F

on

E.cgeneralhid = F.cgeneralhid

WHERE E.PK_ORG <> E.csaleorgoid

AND NVL(F.DR,0) = 0 AND NVL(E.DR,0) = 0

) x

left join

bd_material y

on x.pk_material = y.pk_material

left join

bd_materialstock z

on

x.pk_material = z.pk_material

where

z.pk_org not in('00011A10000000002ST4' ,'00011A10000000002T2L','00011A10000000002T1J')

and z.innermoveprice is null

select a.vbillcode ,b.name,'常数' as 常数查询

from so_saleorder a left join bd_psndoc b

on a.CEMPLOYEEID = b.pk_psndoc

where nvl(a.dr,0) = 0

order by 1 desc,2 desc

select 2+5 from dual --dual表

select vbillcode from so_saleorder where vbillcode like '201812%' --左匹配

select vbillcode from so_saleorder where vbillcode like '%201812' --右匹配

select vbillcode from so_saleorder where vbillcode like '%201812%' --中间匹配

MySQL学习系列之十五——MySQL插入数据

MySQL学习系列之八——分组数据