首页 新闻 会员 周边 捐助

关于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这样越来越多,也越来越长...

请问大神能如何优化修改这段代码?

SQL
问题补充:

需求就是查询多个表里面的字段,但是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里面越来越多越长;有没有什么办法优化?

AaronLi的主页 AaronLi | 初学一级 | 园豆:41
提问于:2015-10-08 08:54
< >
分享
最佳答案
0

结贴结贴结贴

AaronLi | 初学一级 |园豆:41 | 2018-07-06 11:44
其他回答(2)
0

不如把你为什么写这个sql的需求说出来,可能能看得懂点,只看你这个sql 不知道你要什么结果

海神解说 | 园豆:245 (菜鸟二级) | 2015-10-08 15:00
0

赢需求。看来对没一个字段都需要分组。没有别的办法。除非说出具体的需求。

lucika.zh | 园豆:54 (初学一级) | 2015-10-08 16:24
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册