with as insert SQL

最新推荐文章于 2023-07-23 19:04:22 发布

原创最新推荐文章于 2023-07-23 19:04:22 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

SQLServer 专栏收录该内容

15 篇文章

订阅专栏

本文提供了一个复杂的SQL查询案例，通过WITH子句定义多个临时视图，然后进行联表查询以获取订单详情及相关物料信息。

with  a as (
 
 select orderid,matid,matgroup from ps1 where matgroup like 'B01%'
 ),
 b as (
  select orderid,matid,matgroup from ps1 where matgroup like 'H0%'
 ),
  c as (
  select orderid,matid,matgroup from ps1 where matgroup like 'B02%'
 ),
  d as (
  select orderid,matid,matgroup from ps1 where matgroup like 'B04%'
 ),
  e as (
  select orderid,matid,matgroup from ps1 where matgroup like 'B05%'
 ),
  f as (
  select orderid,matid,matgroup from ps1 where matgroup like 'B06%'
 )
 
 select liw3.seq,liw3.orderid,liw3.line,liw3.order_stime,
 liw3.order_etime,liw3.a_stime,liw3.a_etime,liw3.num,liw3.pid,a.matid bottle  
 ,b.matid beverage   ,c.matid cap 
 ,d.matid closure , e.matid lable, f.matid box  from  liw3 
 left join a on liw3.orderid=a.orderid    
 
 left join b on liw3.orderid=b.orderid 
 
 left join c on liw3.orderid=c.orderid 
 
 left join d on liw3.orderid=d.orderid 
 
 left join e on liw3.orderid=e.orderid 
 left join f on liw3.orderid=f.orderid

with  a as (
 
 select orderid,matid,matgroup from ps1 where matgroup like 'B01%'
 ),
 b as (
  select orderid,matid,matgroup from ps1 where matgroup like 'H0%'
 ),
  c as (
  select orderid,matid,matgroup from ps1 where matgroup like 'B02%'
 ),
  d as (
  select orderid,matid,matgroup from ps1 where matgroup like 'B04%'
 ),
  e as (
  select orderid,matid,matgroup from ps1 where matgroup like 'B05%'
 ),
  f as (
  select orderid,matid,matgroup from ps1 where matgroup like 'B06%'
 )
 
 select liw3.seq,liw3.orderid,liw3.line,liw3.order_stime,
 liw3.order_etime,liw3.a_stime,liw3.a_etime,liw3.num,liw3.pid,a.matid bottle  
 ,b.matid beverage   ,c.matid cap 
 ,d.matid closure , e.matid lable, f.matid box  from  liw3 
 left join a on liw3.orderid=a.orderid    
 
 left join b on liw3.orderid=b.orderid 
 
 left join c on liw3.orderid=c.orderid 
 
 left join d on liw3.orderid=d.orderid 
 
 left join e on liw3.orderid=e.orderid 
 left join f on liw3.orderid=f.orderid