一条SQL话语的优化_Sql Server

一条SQL语句的优化
SQL Servier 2005 的数据库，看这条语句能不能帮我优化下，主要是以提高性能为核心进行优化，谢谢。

SQL code

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.amountORDER BY sp.id

------解决方案--------------------
st_storage表只留一个，然后其他状态通过在select语句中case when来判断，
------解决方案--------------------

SQL code

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.amountORDER BY sp.id
------解决方案--------------------
SQL codeSELECT  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 spLEFT JOIN st_storage ta1 ON sp.id=ta1.productidGROUP BY sp.id,sp.name,sp.amountORDER BY sp.id