一条SQL语句的优化

[kevin#1]

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

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

[發糞塗牆]

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

[xuam]

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

[汤姆克鲁斯]

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

[kevin#1]

[Quote=引用 2 楼 的回复:]

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.i……
[/Quote]

谢谢

[kevin#1]

[Quote=引用 3 楼 的回复:]

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),
su……
[/Quote]
谢谢

[發糞塗牆]

呵呵，我好像很不厚道哦，只说不做

[shoppo0505]

如果表格结构不做改变，只是想优化语句的话，应该是没有可能了。