请问这样的嵌套语句还能优化吗

qq_33732665 2018-12-04 05:34:43
请问这样的嵌套语句还能优化吗,特别是标红的 两张表的数据都比较大 谢谢
SELECT
ROW_NUMBER() OVER(Order by o.orderid ) AS ID,
0 as ReportType,

o.orderid,
pc.realname,
pc.Category,
o.Payamount ,--应收款
o.ThirdPartyFee,
o.orderstatus,
o.ProcessStatus,
o.printtickettime ,--出票日期
o.OrderDate,--订单日期
o.createoid,
o.cid,o.allport,
--(select dbo.Fn_Get_FlightAllPortName(o.orderid)) as AllPortName,
o.isonline,
o.CName,
pc.UserID,
pco.clearingtheway AS BalanceType,
pco.CorpShortName,
o.OrderNumber,
o.RealAcceptDatetime,
o.CollectionTime,
o.RealPayDatetime,
ISNULL(O.backAmount,0) as backAmount,
o.IsInter,
(select count(0) from flt_refund (nolock) where orderid=O.OrderID) as refundCount,
(select count(0) from Flt_ZhiJi with(nolock) where orderid=O.OrderID) as HasZhiJiCount,
(select count(0) from flt_modorder with(nolock) where orderid=O.OrderID and orderstatus not in('w','c','n')) as HasModCount
,(select dbo.Fn_Get_Flt_Order_Profit(O.OrderID)) as OpProfit --员工利润
from flt_order o (nolock)
left join flt_orderunion ou(nolock) on ou.orderid=o.orderid
left join P_customer pc (nolock) on pc.cid=o.cid
left join P_Corporation (nolock) pco on pc.corpid=pco.corpid
left join P_CustomerUnion PCU (nolock) on pc.cid=PCU.cid
left join Flt_Route fr (nolock) on o.routeid=fr.routeid
left join P_Operator PO (nolock)on po.oid=o.createoid
left join Flt_Order_ProjectName PJ (nolock) on PJ.ProjectID=OU.ProjectID

WHERE o.orderstatus not in('N','C') and o.PrintTicketTime>='09 4 2018 12:00AM' and o.PrintTicketTime<='12 4 2018 11:59PM'
...全文
172 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
sinat_36542645 2018-12-08
  • 打赏
  • 举报
回复
嵌套得很复杂呢 是不是可以考虑逻辑上优化加语法优化相结合
卖水果的net 2018-12-04
  • 打赏
  • 举报
回复
(select count(0) from flt_refund (nolock) where orderid=O.OrderID) as refundCount, flt_refund 表, orderid 列上有索引吗? 如果没有,你要加上。 如果还慢,可以考虑,提前把这这些数据都 count 到一个临时表,再和 flt_order 做关联查询。

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧