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
(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
select * into #table1 from table.
#table1 就是临时表
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