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