大家帮帮忙

[火狐2017]

有这么一张关于设备情况的表，CPU,设备类型，状态等字段，我现在需要对表就行汇总，比如说
型号 总数 类型1 类型2 使用中 未使用
AMD750 10 2 8 9 1
AMD1.7G 11 3 8 9 2
AMD2.1G 12 4 8 9 3

想通过写一个SQL语句就能得到上面的结果，有这种可能吗？

[pengdali]

可以！

[wunderkind]

这样就可以了。
select tablename.型号,sum(数量1) as 总数,sum(类型1) as 类型1 from tablename group by tablename.型号

[zhigangsun]

up

[火狐2017]

问题是设备类型和状态是一个char字段，高手们帮帮忙啊

[火狐2017]

问题本人已经解决，谢谢各位的帮助。方法如下：
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

[joygxd]

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 求数据的总和