问题本人已经解决,谢谢各位的帮助。方法如下: SELECT Cpu, COUNT(*) AS total, SUM(CASE Type WHEN '01' THEN 1 ELSE 0 END) AS pc_cnt, SUM(CASE Type WHEN '02' THEN 1 ELSE 0 END) AS sv_cnt, SUM(CASE Type WHEN '01' THEN (CASE status WHEN '02' THEN 1 ELSE 0 END) ELSE 0 END), SUM(CASE Type WHEN '02' THEN (CASE status WHEN '02' THEN 1 ELSE 0 END) ELSE 0 END) FROM test GROUP BY CPU
SELECT REEL_MST_STAMPING.P_LOT_NO, REEL_MST_STAMPING.STOCK_CODE, REEL_MST_STAMPING.CURR_QTY, QC_MST_STAMPING.FINISH_QTY , CASE WHEN QC_MST_STAMPING.STATUS='A' THEN QC_MST_STAMPING.FINISH_QTY ELSE 0 END AS ACC_QTY FROM FS_STAMPING, QC_MST_STAMPING, REEL_MST_STAMPING, STOCK_MST WHERE ( QC_MST_STAMPING.QC_NO = REEL_MST_STAMPING.PQC_NO ) and ( FS_STAMPING.STF_NO = REEL_MST_STAMPING.STF_NO ) and ( STOCK_MST.STOCK_CODE = REEL_MST_STAMPING.STOCK_CODE ) GROUP BY REEL_MST_STAMPING.P_LOT_NO, REEL_MST_STAMPING.STOCK_CODE, REEL_MST_STAMPING.CURR_QTY, QC_MST_STAMPING.FINISH_QTY , QC_MST_STAMPING.STATUS ORDER BY REEL_MST_STAMPING.P_LOT_NO ASC, REEL_MST_STAMPING.STOCK_CODE ASC 结果 --------------------------------------- 00045 AAD-0020S .014 .014 .014 00045 AAD-0020S .051 .051 .051 00045 AAD-0020S .060 .060 .060 00045 AAD-0020S 8.315 8.315 8.315 0016 BXM-0101S .003 .003 .003 0016 BXM-0101S 11.000 11.000 11.000 0016 BXM-0101S 249.000 249.000 .000 0016 BXM-0101S 7410.016 7410.016 7410.016 0022 ABS-0019S 14157.000 14157.000 14157.000 0034 BYD-0061S .019 .019 .019 --------------------------------------------------------------------请教怎样按p_lot_no 求数据的总和
SELECT Cpu, COUNT(*) AS total, SUM(CASE Type WHEN '01' THEN 1 ELSE 0 END)
AS pc_cnt, SUM(CASE Type WHEN '02' THEN 1 ELSE 0 END) AS sv_cnt,
SUM(CASE Type WHEN '01' THEN (CASE status WHEN '02' THEN 1 ELSE 0 END)
ELSE 0 END),
SUM(CASE Type WHEN '02' THEN (CASE status WHEN '02' THEN 1 ELSE 0 END)
ELSE 0 END)
FROM test
GROUP BY CPU
REEL_MST_STAMPING.STOCK_CODE,
REEL_MST_STAMPING.CURR_QTY,
QC_MST_STAMPING.FINISH_QTY ,
CASE
WHEN QC_MST_STAMPING.STATUS='A' THEN QC_MST_STAMPING.FINISH_QTY
ELSE 0
END AS ACC_QTY
FROM FS_STAMPING,
QC_MST_STAMPING,
REEL_MST_STAMPING,
STOCK_MST
WHERE ( QC_MST_STAMPING.QC_NO = REEL_MST_STAMPING.PQC_NO ) and
( FS_STAMPING.STF_NO = REEL_MST_STAMPING.STF_NO ) and
( STOCK_MST.STOCK_CODE = REEL_MST_STAMPING.STOCK_CODE )
GROUP BY REEL_MST_STAMPING.P_LOT_NO,
REEL_MST_STAMPING.STOCK_CODE,
REEL_MST_STAMPING.CURR_QTY,
QC_MST_STAMPING.FINISH_QTY ,
QC_MST_STAMPING.STATUS
ORDER BY REEL_MST_STAMPING.P_LOT_NO ASC,
REEL_MST_STAMPING.STOCK_CODE ASC 结果
---------------------------------------
00045 AAD-0020S .014 .014 .014
00045 AAD-0020S .051 .051 .051
00045 AAD-0020S .060 .060 .060
00045 AAD-0020S 8.315 8.315 8.315
0016 BXM-0101S .003 .003 .003
0016 BXM-0101S 11.000 11.000 11.000
0016 BXM-0101S 249.000 249.000 .000
0016 BXM-0101S 7410.016 7410.016 7410.016
0022 ABS-0019S 14157.000 14157.000 14157.000
0034 BYD-0061S .019 .019 .019
--------------------------------------------------------------------请教怎样按p_lot_no 求数据的总和