关于SQL查询语句的优化疑问
0
[已解决问题]
解决于 2018-07-06 11:45
select pt.PartNum, pt.PartDescription, pt.JobNum ,jh.Character03, od.SalesCatID, jh.Character02, jh.Character04, jh.Character05, jh.Character06, od.Character07 ,pg.Description,oh.Character03,od.Character06 ,c.CustID ,orl.SellingReqQty , (orl.SellingJobShippedQty + orl.SellingStockShippedQty) , (orl.SellingReqQty-(orl.SellingJobShippedQty+orl.SellingStockShippedQty)) , (orl.SellingReqQty- jpt.ReceivedQty) , jpt.ReceivedQty ,od.Number02 ,p.Number07 , (case when od.Number01>0 then od.Number01 else od.DocUnitPrice end) , min(pt.TranDate) as "第一件时间" ,max(pt.TranDate) as "最后一件时间" from PUB.PartTran pt inner join pub.JobHead jh on pt.Company =jh.Company and pt.JobNum = jh.JobNum inner join pub.Part p on pt.Company=p.Company and pt.PartNum=p.PartNum inner join pub.ProdGrup pg on p.ProdCode=pg.ProdCode and pg.Company=p.Company inner join pub.JobProd jp on pt.Company=jp.Company and pt.JobNum =jp.JobNum inner join pub.OrderHed oh on jp.Company=oh.Company and jp.OrderNum=oh.OrderNum inner join pub.OrderDtl od on jp.Company=od.Company and jp.OrderNum=od.OrderNum and jp.OrderLine=od.OrderLine inner join pub.Customer c on oh.Company=c.Company and oh.CustNum=c.CustNum inner join pub.OrderRel orl on jp.Company=orl.Company and jp.OrderNum=orl.OrderNum and jp.OrderLine=orl.OrderLine and jp.OrderRelNum=orl.OrderRelNum inner join pub.JobPart jpt on pt.JobNum=jpt.JobNum and pt.Company=jpt.Company where pt.company = '001' and pt.TranType = 'MFG' and pt.WareHouseCode = 50 and jh.ReqDueDate >= '2015-07-01' and jh.ReqDueDate <='2015-07-31' group by pt.PartNum, pt.PartDescription, pt.JobNum,jh.Character03, od.SalesCatID, jh.Character02 , jh.Character04 , jh.Character05 , jh.Character06, od.Character07, pg.Description, oh.Character03, od.Character06, c.CustID, orl.SellingReqQty, (orl.SellingJobShippedQty + orl.SellingStockShippedQty), (orl.SellingReqQty-(orl.SellingJobShippedQty+orl.SellingStockShippedQty)), (orl.SellingReqQty- jpt.ReceivedQty), jpt.ReceivedQty, od.Number02, p.Number07, (case when od.Number01>0 then od.Number01 else od.DocUnitPrice end)
每次增加一个查询字段就需要在 group by 后再增加一个分组，group by这样越来越多，也越来越长...
请问大神能如何优化修改这段代码？
问题补充：
需求就是查询多个表里面的字段，但是select里有max和min函数，所以要用到group by对吧。
用到group by，它又要把select里面的所有字段都添加到分组。
像这个代码:
(case when od.Number01>0 then od.Number01 else od.DocUnitPrice end)
select里面需要，又要写到group by里面，导致group by里面越来越多越长；有没有什么办法优化？
最佳答案
0
结贴结贴结贴
其他回答(2)
0
不如把你为什么写这个sql的需求说出来，可能能看得懂点，只看你这个sql 不知道你要什么结果
0
赢需求。看来对没一个字段都需要分组。没有别的办法。除非说出具体的需求。
