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%' --中间匹配
