SELECT  
sp.id, sp.name, sp.amount,
COUNT(ta1.id) AS unsubmitSum, COUNT(ta2.id) AS submitSum, COUNT(ta3.id) AS rukuSuccessSum, COUNT(ta4.id) AS rukuFailSum,
COUNT(ta5.id) AS onShelfSum, COUNT(ta6.id) AS delSum, COUNT(ta7.id) AS prepareSum, COUNT(ta8.id) AS unreturnSum
 FROM st_product sp
 LEFT JOIN st_storage ta1 ON sp.id=ta1.productid AND ta1.state='未提交'
 LEFT JOIN st_storage ta2 ON sp.id=ta2.productid AND ta2.state='已提交'
 LEFT JOIN st_storage ta3 ON sp.id=ta3.productid AND ta3.state='入库成功'
 LEFT JOIN st_storage ta4 ON sp.id=ta4.productid AND ta4.state='入库失败'
 LEFT JOIN st_storage ta5 ON sp.id=ta5.productid AND ta5.state='已上架'
 LEFT JOIN st_storage ta6 ON sp.id=ta6.productid AND ta6.state='删除'
 LEFT JOIN st_storage ta7 ON sp.id=ta7.productid AND ta7.state='备货成功'
 LEFT JOIN st_storage ta8 ON sp.id=ta8.productid AND ta8.state='待退货'
GROUP BY sp.id,sp.name,sp.amount
ORDER BY sp.id

SELECT  
sp.id, sp.name, sp.amount,sum(case ta1.state when '未提交' then 1 else 0 end)AS unsubmitSum ,
......
FROM st_product sp
 LEFT JOIN st_storage ta1 ON sp.id=ta1.productid 
GROUP BY sp.id,sp.name,sp.amount
ORDER BY sp.id

------解决方案--------------------
SQL code
SELECT  
sp.id, sp.name, sp.amount,
sum(case when ta1.state='未提交' then 1 else 0 end),
sum(case when ta1.state='已提交' then 1 else 0 end),
sum(case when ta1.state='入库成功' then 1 else 0 end),
sum(case when ta1.state='入库失败' then 1 else 0 end),
sum(case when ta1.state='已上架' then 1 else 0 end),
sum(case when ta1.state='删除' then 1 else 0 end),
sum(case when ta1.state='备货成功' then 1 else 0 end),
sum(case when ta1.state='待退货' then 1 else 0 end)
FROM st_product sp
LEFT JOIN st_storage ta1 ON sp.id=ta1.productid
GROUP BY sp.id,sp.name,sp.amount
ORDER BY sp.id