关于直接SQL 查询条件问题
1人赞赏了该文章
1,049次浏览
编辑于2015年09月15日 17:00:35
select TOP(10000000) y.SCDDRQ as 订单日期,y.SCDDBM as 订单编码,y.JHGZH as 计划跟踪号,y.DDHH as 订单行号,y.WLBM as 物料编码,y.JBM as 旧编码,
y.WLMC as 物料名称,y.wldj as 等级 , y.WLDS as 底色,y.WLWL as 纹路 , y.AABH as 安安编码 ,y.WLPH as 批号 ,y.SCX as 生产线 ,y.SCCJ as 生产车间,
y.DDSL as 订单数量, y.HBDH as 汇报单号, Y.HBSL as 汇报数量 ,u.RKDH as 入库单号 ,U.RKDJRQ AS 入库单据日期,
U.RKDZDRQ 入库制单日期,u.RKZSHRQ as 入库单审核日期,
u.A级 as A级入库数量
,u.B级 as B级入库数量,u.C级 as C级入库数量,u.O级 as O级入库数量,u.H级 as H级入库数量,u.其他 as 浆料入库数量,
r.jydbm as 检验单编号,r.jydrq as 检验日期,R.zjsl as 检验数量
from (select a.FDATE as SCDDRQ, a.FBILLNO as SCDDBM ,b.FMTONO as JHGZH, b.FSEQ as DDHH,c.FNUMBER as WLBM,c.FOLDNUMBER as JBM,
d.FNAME as WLMC,aa.FDJ as WLDJ, aa.FDise as WLDS ,aa.FWl as WLWL,aa.AANo as AABH,b.FLOT_TEXT
as WLPH,bm.FNAME as SCX ,bmc.fname as SCCJ,b.FQTY as DDSL,e1.FBILLNO as HBDH,e.SFQUAQTY AS HBSL
from T_PRD_MO as a
join T_PRD_MOENTRY as b on a.FID =b.fid
left outer join t_bd_material as c on c.FMATERIALID =b.FMATERIALID
left outer join t_bd_material_l as d on d.FMATERIALID=c.FMATERIALID
left outer join AAA_V_FLEXSITEMDETAILV as aa on b.FAUXPROPID=aa.fid
left outer join t_bd_department_l as bm on bm. FDEPTID = b.F_AAA_BASE
left outer join t_bd_department_l as bmc on bmc. FDEPTID = b.FWORKSHOPID
---联结基础信息
left join (select W.FSRCENTRYID,W.FID , w.FSRCENTRYSEQ ,sum( FQUAQTY )as SFQUAQTY from T_PRD_MORPTENTRY as W
group by W.FSRCENTRYID,W.FID ,w.FSRCENTRYSEQ ) as e
on e.FSRCENTRYID=B.FENTRYID
left outer join T_PRD_MORPT as e1 on e1.FID=e.FID
---联结汇报单汇总
) as Y
left outer join
(select A1.FBILLNO as RKDH,A1.FDATE AS RKDJRQ,d.FNUMBER as RKWLBM,a.FMATERIALID as RKWLNM ,a.FSRCBILLNO as RKHBDH ,
A1.FCREATEDATE AS RKDZDRQ ,
A1.FAPPROVEDATE AS RKZSHRQ,
sUM(Case When AA.FDJ='A级' then A.FREALQTY else 0 End) A级,
sUM(Case When AA.FDJ='B级' then A.FREALQTY else 0 End) B级,
sUM(Case When AA.FDJ='C级' then A.FREALQTY else 0 End) C级,
sUM(Case When AA.FDJ='O级' then A.FREALQTY else 0 End) O级,
sUM(Case When AA.FDJ='H级' then A.FREALQTY else 0 End) H级,
sUM(Case When isnull(AA.FDJ,'')='' then A.FREALQTY else 0 End) 其他
from T_PRD_INSTOCKENTRY as A
Left Join AAA_V_FLEXSITEMDETAILV AS AA On A.FAUXPROPID=AA.FID
join T_PRD_INSTOCK as A1 ON A1.FID=A.FID
left outer join t_bd_material as d on d.FMATERIALID =a.FMATERIALID
GROUP BY A1.FBILLNO,A.FID ,a.FMATERIALID,a.FSRCBILLNO,d.FNUMBER,A1.FCREATEDATE,A1.FAPPROVEDATE,A1.FDATE) AS U
ON U.RKWLBM=Y.WLBM AND U.RKHBDH=Y.HBDH
---联结入库单等级汇总
left outer join (select z.JHBDH as jyhbdh,wl.FNUMBER as jywlbm ,q.FBILLNO as jydbm ,q.FDATE as jydrq ,z.jysl as zjsl from(select t.FID, t.FMATERIALID as JWLBM,t.FSRCBILLNO as JHBDH ,sum(FBASEINSPECTQTY) as jysl from T_QM_INSPECTBILLENTRY_A as t
group by t.FID, t.FMATERIALID,t.FSRCBILLNO ) as z
join T_QM_INSPECTBILL as q on z.fid = q.FID
left outer join t_bd_material as wl on wl.FMATERIALID = z.JWLBM) as R
ON R.jyhbdh = Y.HBDH AND R.jywlbm = Y.WLBM
where y.WLBM not like '0522%' and y.SCDDRQ >= '#StartDate#' and y.SCDDRQ <='#EndDate#'
and y.SCDDBM like '%#生产订单编号#%'
and y.JHGZH like '%#FMtoNo#%'
and y.SCX like '%#bmFNAME#%'
and y.SCCJ like '%#bmcFNAME#%'
and y.WLPH like '%#FLotNumber#%'
order by y.SCDDBM,y.DDHH
y.WLMC as 物料名称,y.wldj as 等级 , y.WLDS as 底色,y.WLWL as 纹路 , y.AABH as 安安编码 ,y.WLPH as 批号 ,y.SCX as 生产线 ,y.SCCJ as 生产车间,
y.DDSL as 订单数量, y.HBDH as 汇报单号, Y.HBSL as 汇报数量 ,u.RKDH as 入库单号 ,U.RKDJRQ AS 入库单据日期,
U.RKDZDRQ 入库制单日期,u.RKZSHRQ as 入库单审核日期,
u.A级 as A级入库数量
,u.B级 as B级入库数量,u.C级 as C级入库数量,u.O级 as O级入库数量,u.H级 as H级入库数量,u.其他 as 浆料入库数量,
r.jydbm as 检验单编号,r.jydrq as 检验日期,R.zjsl as 检验数量
from (select a.FDATE as SCDDRQ, a.FBILLNO as SCDDBM ,b.FMTONO as JHGZH, b.FSEQ as DDHH,c.FNUMBER as WLBM,c.FOLDNUMBER as JBM,
d.FNAME as WLMC,aa.FDJ as WLDJ, aa.FDise as WLDS ,aa.FWl as WLWL,aa.AANo as AABH,b.FLOT_TEXT
as WLPH,bm.FNAME as SCX ,bmc.fname as SCCJ,b.FQTY as DDSL,e1.FBILLNO as HBDH,e.SFQUAQTY AS HBSL
from T_PRD_MO as a
join T_PRD_MOENTRY as b on a.FID =b.fid
left outer join t_bd_material as c on c.FMATERIALID =b.FMATERIALID
left outer join t_bd_material_l as d on d.FMATERIALID=c.FMATERIALID
left outer join AAA_V_FLEXSITEMDETAILV as aa on b.FAUXPROPID=aa.fid
left outer join t_bd_department_l as bm on bm. FDEPTID = b.F_AAA_BASE
left outer join t_bd_department_l as bmc on bmc. FDEPTID = b.FWORKSHOPID
---联结基础信息
left join (select W.FSRCENTRYID,W.FID , w.FSRCENTRYSEQ ,sum( FQUAQTY )as SFQUAQTY from T_PRD_MORPTENTRY as W
group by W.FSRCENTRYID,W.FID ,w.FSRCENTRYSEQ ) as e
on e.FSRCENTRYID=B.FENTRYID
left outer join T_PRD_MORPT as e1 on e1.FID=e.FID
---联结汇报单汇总
) as Y
left outer join
(select A1.FBILLNO as RKDH,A1.FDATE AS RKDJRQ,d.FNUMBER as RKWLBM,a.FMATERIALID as RKWLNM ,a.FSRCBILLNO as RKHBDH ,
A1.FCREATEDATE AS RKDZDRQ ,
A1.FAPPROVEDATE AS RKZSHRQ,
sUM(Case When AA.FDJ='A级' then A.FREALQTY else 0 End) A级,
sUM(Case When AA.FDJ='B级' then A.FREALQTY else 0 End) B级,
sUM(Case When AA.FDJ='C级' then A.FREALQTY else 0 End) C级,
sUM(Case When AA.FDJ='O级' then A.FREALQTY else 0 End) O级,
sUM(Case When AA.FDJ='H级' then A.FREALQTY else 0 End) H级,
sUM(Case When isnull(AA.FDJ,'')='' then A.FREALQTY else 0 End) 其他
from T_PRD_INSTOCKENTRY as A
Left Join AAA_V_FLEXSITEMDETAILV AS AA On A.FAUXPROPID=AA.FID
join T_PRD_INSTOCK as A1 ON A1.FID=A.FID
left outer join t_bd_material as d on d.FMATERIALID =a.FMATERIALID
GROUP BY A1.FBILLNO,A.FID ,a.FMATERIALID,a.FSRCBILLNO,d.FNUMBER,A1.FCREATEDATE,A1.FAPPROVEDATE,A1.FDATE) AS U
ON U.RKWLBM=Y.WLBM AND U.RKHBDH=Y.HBDH
---联结入库单等级汇总
left outer join (select z.JHBDH as jyhbdh,wl.FNUMBER as jywlbm ,q.FBILLNO as jydbm ,q.FDATE as jydrq ,z.jysl as zjsl from(select t.FID, t.FMATERIALID as JWLBM,t.FSRCBILLNO as JHBDH ,sum(FBASEINSPECTQTY) as jysl from T_QM_INSPECTBILLENTRY_A as t
group by t.FID, t.FMATERIALID,t.FSRCBILLNO ) as z
join T_QM_INSPECTBILL as q on z.fid = q.FID
left outer join t_bd_material as wl on wl.FMATERIALID = z.JWLBM) as R
ON R.jyhbdh = Y.HBDH AND R.jywlbm = Y.WLBM
where y.WLBM not like '0522%' and y.SCDDRQ >= '#StartDate#' and y.SCDDRQ <='#EndDate#'
and y.SCDDBM like '%#生产订单编号#%'
and y.JHGZH like '%#FMtoNo#%'
and y.SCX like '%#bmFNAME#%'
and y.SCCJ like '%#bmcFNAME#%'
and y.WLPH like '%#FLotNumber#%'
order by y.SCDDBM,y.DDHH
推荐阅读