SELECT CASE ISNULL(i.inttype,0) WHEN 5 THEN 1 ELSE 0 END AS 是否委外材料出库, i.issuemainid, ISNULL(i.monthplanmainid,0) AS monthplanmainid, ISNULL(i.billnumber,'') AS billnumber, ISNULL(i.mnumberhead,'') AS mnumberhead, ISNULL(i.mnamehead,'') AS mnamehead, ISNULL(i.billnum,'') AS billnum, ISNULL(i.billdate,0) AS billdate, i.mnumber, i.mname, i.model, i.primaryunitname, i.issuequan AS 申请数量, ISNULL(m.出库数量,0) AS 出库数量, i.issuequan - ISNULL(m.出库数量,0) AS 未出库数量, ISNULL(i.issuesubid,0) AS issuesubid, ISNULL(i.re,'') AS re, ISNULL(i.checker,'') AS checker, ISNULL(i.suppliercode,'') AS suppliercode, ISNULL(i.suppliername,'') AS suppliername, ISNULL(i.weituo_mnumber,'') AS weituo_mnumber, ISNULL(i.weituo_mname,'') AS weituo_mname FROM kf_v_issueapply_生产领用申请 i LEFT JOIN ( SELECT issueid AS issuemainid, mnumber, mname, model, primaryunitname, SUM(factissuequan) AS 出库数量 FROM kf_v_mateout_生产领用出库 GROUP BY issuemainid,mnumber,mname,model) m ON m.issuemainid = i.issuemainid AND m.mnumber = i.mnumber AND m.mname = i.mname AND m.model = i.model WHERE i.申请数量 > ISNULL(m.出库数量,0)
2.如果内部数据经常需要修改,不易建立视图
WHEN 5 THEN 1
ELSE 0
END AS 是否委外材料出库,
i.issuemainid,
ISNULL(i.monthplanmainid,0) AS monthplanmainid,
ISNULL(i.billnumber,'') AS billnumber,
ISNULL(i.mnumberhead,'') AS mnumberhead,
ISNULL(i.mnamehead,'') AS mnamehead,
ISNULL(i.billnum,'') AS billnum,
ISNULL(i.billdate,0) AS billdate,
i.mnumber,
i.mname,
i.model,
i.primaryunitname,
i.issuequan AS 申请数量,
ISNULL(m.出库数量,0) AS 出库数量,
i.issuequan
- ISNULL(m.出库数量,0) AS 未出库数量,
ISNULL(i.issuesubid,0) AS issuesubid,
ISNULL(i.re,'') AS re,
ISNULL(i.checker,'') AS checker,
ISNULL(i.suppliercode,'') AS suppliercode,
ISNULL(i.suppliername,'') AS suppliername,
ISNULL(i.weituo_mnumber,'') AS weituo_mnumber,
ISNULL(i.weituo_mname,'') AS weituo_mname
FROM kf_v_issueapply_生产领用申请 i
LEFT JOIN ( SELECT issueid AS issuemainid,
mnumber,
mname,
model,
primaryunitname,
SUM(factissuequan) AS 出库数量
FROM kf_v_mateout_生产领用出库
GROUP BY issuemainid,mnumber,mname,model) m
ON m.issuemainid = i.issuemainid
AND m.mnumber = i.mnumber
AND m.mname = i.mname
AND m.model = i.model
WHERE i.申请数量 > ISNULL(m.出库数量,0)
没看懂,这么看出来楼主代码等价于你这个。要是他的UNION 后面的查询
存在mnumber ,
Mname,
Model,
PrimaryUnitName ,这些列在UNION 前面的列不存在。你这个不是删除了
跑了下结果:
服务器: 消息 207,级别 16,状态 3,行 1
列名 'issuemainid' 无效。
服务器: 消息 207,级别 16,状态 1,行 1
列名 '申请数量' 无效。