求SQL语句优化（每次执行者个语句都要9秒出结果）

[lincolndai]

select [u.tpp] as '工号', ISNULL(U.PP,0)as '出票订单数量',ISNULL(U.BBB,0)as '暂不能出票量',ISNULL(U.CCC,0)as '取消订单量',ISNULL(U.DDD,0)as'平均出票时间', ISNULL(X.EEE,0)as'出票金额' FROM
(select  Y.TP as [u.tpp], Y.PP,Y.BBB,Y.CCC,R.DDD FROM
  (select T.P as TP,T.PP,T.BBB,E.CCC from
     (select Q.AA as P,Q.AAA as PP,ISNULL(W.BBB,0) as BBB from
		(select aa.a as AA,count(aa.b) as AAA from(select orderlog.value('(//root/log[role="P"][station="A"]/account)[1]','varchar(20)') as A ,OrderLog.value('(//root/log[station="E"]/station)[1]','varchar(max)')as b  FROM tblOrderLogs where OrderLog.value('(root/log/addtime)[2]','datetime') between '2011-11-17 00:00:00' and '2011-11-17 23:59:59' and OrderLog.value('(//root/log[station="E"]/station)[1]','varchar(max)') is not null and orderlog.value('(//root/log[role="P"][station="A"]/account)[1]','varchar(20)') is not null)as aa group by aa.A)  as Q left join
	    (select aa.A as BB,count(aa.B) as BBB from(select orderlog.value('(//root/log[role="P"][station="A"]/account)[1]','varchar(20)') as A ,OrderLog.value('(//root/log[station="K"]/station)[1]','varchar(max)')as b  FROM tblOrderLogs where OrderLog.value('(root/log/addtime)[2]','datetime') between '2011-11-17 00:00:00' and '2011-11-17 23:59:59' and OrderLog.value('(//root/log[station="K"]/station)[1]','varchar(max)') is not null and orderlog.value('(//root/log[role="P"][station="A"]/account)[1]','varchar(20)') is not null)as aa group by aa.A ) as W on Q.AA=W.BB )as T left join 
        (select aa.A as CC,count(aa.B) as CCC from(select orderlog.value('(//root/log[role="P"][station="A"]/account)[1]','varchar(20)') as A ,OrderLog.value('(//root/log[station="O"]/station)[1]','varchar(max)')as b  FROM tblOrderLogs where OrderLog.value('(root/log/addtime)[2]','datetime') between '2011-11-17 00:00:00' and '2011-11-17 23:59:59' and OrderLog.value('(//root/log[station="O"]/station)[1]','varchar(max)') is not null and orderlog.value('(//root/log[role="P"][station="A"]/account)[1]','varchar(20)') is not null)as aa group by aa.A ) as E ON T.P=E.CC )as Y left join
        (select aa.A as DD,DDD=sum(diff)/count(aa.b) from(select diff=DATEDIFF(minute ,paytime,outtime),OrderLog.value('(root/log[role="P"][station="A"]/account)[1]','varchar(20)')as a ,OrderLog.value('(//root/log[station="E"]/station)[1]','varchar(max)')as b from tblOrderLogs,tblorderbook,orderext  where tblOrderLogs.OrderID in(select  OrderID FROM dbo.tblOrderBook where orderstate='e') and outtime between '2011-11-17 00:00:00' and '2011-11-17 23:59:59' and orderext.orderno=tblorderbook.orderid and tblOrderLogs.orderid=tblorderbook.orderid)as aa group by aa.a) as R  ON Y.TP=R.DD)as U left join 
	    (select aa.A as EE,EEE=sum(aa.b) from(select orderlog.value('(//root/log[role="P"][station="A"]/account)[1]','varchar(20)') as A,(FlightCost * (1 - (AgentRate * 0.01)) * (1 - (AgioRate * 0.01)) + TaxCost + TradingFee )as b from tblOrderLogs,tblorderbook,orderext  where tblOrderLogs.OrderID in(SELECT  OrderID FROM dbo.tblOrderBook where orderstate='e') and outtime between '2011-11-17 00:00:00' and '2011-11-17 23:59:59' and orderext.orderno=tblorderbook.orderid and tblOrderLogs.orderid=tblorderbook.orderid and orderlog.value('(//root/log[role="P"][station="A"]/account)[1]','varchar(20)') is not null)as aa group by aa.a)as x on [U.TPP]=X.EE

[lincolndai]

啊。。临时表用不来。。求高手帮我这个SQL新手把那句SQL语句放临时表吧

[flyfly2008]

create table #aaa

[lincolndai]

查一天的9秒，1个月的半分多种、、、、求大神优化

[--小F--]

哇塞 好多嵌套。

[快溜]

放到临时表里再left join

[Rotel-刘志东]

好多的嵌套呀放入临时表处理应该效率会高一下.

[lincolndai]

怎么用临时表？

[geniuswjt]

有函数，等死...

[uu_lt]

依稀记得 刚开始做sql时我也是这么搞的。然后，知道有临时表这个东东
select * into #table1 from table.
#table1 就是临时表

[lincolndai]

想改成如同这种格式

select count( * ) as totality,sum(number) as amount,UseName from(select count( * ) as E, FlightCost * (1 - (AgentRate * 0.01)) * (1 - (AgioRate * 0.01)) + TaxCost + TradingFee as number, 
UseName FROM(SELECT id, tblOrderBook.Orderid, UseName, OrderState, CreateTime, FlightCost, AgentRate, AgioRate, TaxCost, TradingFee, Bution_Type
 FROM dbo.TbDistribution_log INNER JOIN dbo.tblOrderBook ON dbo.TbDistribution_log.Orderid = dbo.tblOrderBook.OrderID where 1 = 1 and Bution_Type = 'A') as long 
GROUP BY FlightCost, AgentRate, AgioRate, TaxCost, TradingFee, UseName) as bs GROUP BY UseName

[勿勿]

用临时表速度可能好点吧

[petermei]

如果是我做，我会先将需要嵌套的几个表现做成临时表，其中先将那些需要用函数(isnull , count , sum)转换的栏位，先做好！这样会很快的……

[稻庄]

查一天的9秒，1个月的半分多种、、、、求大神优化????


越往后效率不是更低？

[沉默的羔羊]

估计是里面用函数转换花了太多时间。

[yudiw]

嘿嘿，还有这样的写法啊。。。。。
临时表，联结。。。