下面是sql交叉查询 200分(100)

提示qty1无效SELECT a.DINGD_NO as '生产编号',a.ehw_pici as '生产批次',a.ehw_xiadate as '下单日期',a.ehw_xiaqty as '下单数量',a.ehw_zgs as '个取数',SUM(CASE A.GONGX_NO WHEN '辅料' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as qty1,SUM(CASE A.GONGX_NO WHEN '领料' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as qty2,SUM(CASE A.GONGX_NO WHEN '钻孔' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as qty3,SUM(CASE A.GONGX_NO WHEN '镀铜' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as qty4,SUM(CASE A.GONGX_NO WHEN '露光' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as 'qty5,SUM(CASE A.GONGX_NO WHEN '蚀刻' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as 'qty6,SUM(qty1+qty2+qty3+qty4+qty5+qty6) as '合计'GROUP BY a.DINGD_NO,a.ehw_pici,a.ehw_xiadate,a.ehw_xiaqty,a.ehw_zgs

select B.*,(B.qty1+B.qty2+B.qty3+B.qty4+B.qty5+B.qty6 ) as '合计'from (SELECT a.DINGD_NO as '生产编号',a.ehw_pici as '生产批次',a.ehw_xiadate as '下单日期',a.ehw_xiaqty as '下单数量',a.ehw_zgs as '个取数',SUM(CASE a.GONGX_NO WHEN '辅料' THEN (a.EHW_INQTY-a.EHW_OUTQTY) ELSE 0 END) as qty1,SUM(CASE a.GONGX_NO WHEN '领料' THEN (a.EHW_INQTY-a.EHW_OUTQTY) ELSE 0 END) as qty2,SUM(CASE a.GONGX_NO WHEN '钻孔' THEN (a.EHW_INQTY-a.EHW_OUTQTY) ELSE 0 END) as qty3,SUM(CASE a.GONGX_NO WHEN '镀铜' THEN (a.EHW_INQTY-a.EHW_OUTQTY) ELSE 0 END) as qty4,SUM(CASE a.GONGX_NO WHEN '露光' THEN (a.EHW_INQTY-a.EHW_OUTQTY) ELSE 0 END) as qty5,SUM(CASE a.GONGX_NO WHEN '蚀刻' THEN (a.EHW_INQTY-a.EHW_OUTQTY) ELSE 0 END) as qty6From Table001 aGROUP BY a.DINGD_NO,a.ehw_pici,a.ehw_xiadate,a.ehw_xiaqty,a.ehw_zgs) as B

SELECT a.DINGD_NO as '生产编号',a.ehw_pici as '生产批次',a.ehw_xiadate as '下单日期',a.ehw_xiaqty as '下单数量',a.ehw_zgs as '个取数',SUM(CASE A.GONGX_NO WHEN '辅料' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as qty1,SUM(CASE A.GONGX_NO WHEN '领料' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as qty2,SUM(CASE A.GONGX_NO WHEN '钻孔' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as qty3,SUM(CASE A.GONGX_NO WHEN '镀铜' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as qty4,SUM(CASE A.GONGX_NO WHEN '露光' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as 'qty5,SUM(CASE A.GONGX_NO WHEN '蚀刻' THEN (A.EHW_INQTY-A.EHW_OUTQTY) ELSE 0 END) as 'qty6,SUM(case when A.GONGX_NO in ('辅料', '领料', '钻孔', '镀铜', '露光' , '蚀刻') then (A.EHW_INQTY-A.EHW_OUTQTY) else 0 end) as '合计'Table001 aGROUP BY a.DINGD_NO,a.ehw_pici,a.ehw_xiadate,a.ehw_xiaqty,a.ehw_zgs

多人接受答案了。