查询中嵌套子查询

DECLARE @beginTime DATETIME= '2017-12-20 00:00:00';
DECLARE @endTime DATETIME= DATEADD(DAY, 1, @beginTime);
SELECT CONVERT(VARCHAR(10), 日期, 23) 日期 ,
线路 ,
SUM(temp2.二段支付单量) 二段支付单量 ,
SUM(temp2.异常未能下架单量) 异常未能下架单量 ,
SUM(temp2.待下架单量) 待下架单量 ,
SUM(temp2.已下架单量) 已下架单量 ,
SUM(temp2.出库异常单量) 出库异常单量 ,
SUM(temp2.[24小时内出库单量]) [24小时内出库单量] ,
SUM(temp2.[48小时内出库单量]) [48小时内出库单量] ,
SUM(temp2.[48小时外出库单量]) [48小时外出库单量]
FROM ( SELECT @beginTime AS 日期 ,
temp.DestinationLine AS 线路 ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportOrder WITH ( NOLOCK )
WHERE CreateTime > @beginTime
AND CreateTime < @endTime
AND Country = temp.DestinationName
AND InterDeliveryID = temp.LogisticsID
AND StatusFlag > -1
) AS 二段支付单量 ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportOrder WITH ( NOLOCK )
WHERE CreateTime > @beginTime
AND CreateTime < @endTime
AND Country = temp.DestinationName
AND InterDeliveryID = temp.LogisticsID
AND ExceptionFlag > 0
AND StatusFlag > -1
AND StatusFlag < 6
) AS 异常未能下架单量 ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportOrder WITH ( NOLOCK )
WHERE CreateTime > @beginTime
AND CreateTime < @endTime
AND Country = temp.DestinationName
AND InterDeliveryID = temp.LogisticsID
AND StatusFlag > -1
AND StatusFlag < 6
AND ExceptionFlag = 0
) AS 待下架单量 ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportOrder WITH ( NOLOCK )
WHERE CreateTime > @beginTime
AND CreateTime < @endTime
AND Country = temp.DestinationName
AND InterDeliveryID = temp.LogisticsID
AND StatusFlag >= 6
AND StatusFlag < 15
AND ExceptionFlag = 0
) AS 已下架单量 ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportOrder WITH ( NOLOCK )
WHERE CreateTime > @beginTime
AND CreateTime < @endTime
AND Country = temp.DestinationName
AND InterDeliveryID = temp.LogisticsID
AND ExceptionFlag > 0
AND StatusFlag >= 6
AND StatusFlag < 15
) AS 出库异常单量 ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportOrder WITH ( NOLOCK )
WHERE CreateTime > @beginTime
AND CreateTime < @endTime
AND Country = temp.DestinationName
AND InterDeliveryID = temp.LogisticsID
AND StatusFlag >= 15
AND ExceptionFlag = 0
AND DATEDIFF(HOUR, CreateTime, BaggingDate) < 24
) AS [24小时内出库单量] ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportOrder WITH ( NOLOCK )
WHERE CreateTime > @beginTime
AND CreateTime < @endTime
AND Country = temp.DestinationName
AND InterDeliveryID = temp.LogisticsID
AND StatusFlag >= 15
AND ExceptionFlag = 0
AND DATEDIFF(HOUR, CreateTime, BaggingDate) >= 24
AND DATEDIFF(HOUR, CreateTime, BaggingDate) < 48
) AS [48小时内出库单量] ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportOrder WITH ( NOLOCK )
WHERE CreateTime > @beginTime
AND CreateTime < @endTime
AND Country = temp.DestinationName
AND InterDeliveryID = temp.LogisticsID
AND StatusFlag >= 15
AND ExceptionFlag = 0
AND DATEDIFF(HOUR, CreateTime, BaggingDate) >= 48
) AS [48小时外出库单量]
FROM ( SELECT DISTINCT
DestinationName ,
LogisticsID ,
CASE DestinationName
+ CAST(LogisticsID AS VARCHAR(5))
WHEN '澳大利亚89' THEN '澳大利亚'
WHEN '澳大利亚91' THEN '澳大利亚'
WHEN '澳大利亚98' THEN '澳大利亚'
WHEN '澳大利亚105' THEN '澳大利亚'
WHEN '马来西亚85' THEN DestinationName
WHEN '马来西亚103' THEN 'LZD马来西亚空运'
WHEN '马来西亚104' THEN 'LZD马来西亚海运'
WHEN '新加坡103' THEN 'LZD新加坡空运'
WHEN '新加坡104' THEN 'LZD新加坡海运'
WHEN '日本107' THEN DestinationName
WHEN '新加坡85' THEN DestinationName
WHEN '新加坡89' THEN DestinationName
WHEN '新西兰89' THEN DestinationName
WHEN '中国台湾97' THEN DestinationName
WHEN '中国台湾100' THEN DestinationName
WHEN '中国台湾101' THEN '台湾直送'
WHEN '中国台湾102' THEN '台湾直送'
WHEN '中国香港1' THEN '香港'
WHEN '中国香港63' THEN '香港德邦'
WHEN '中国香港95' THEN '香港'
ELSE '其它'
END AS DestinationLine
FROM [Tr.TWX].[dbo].[Sys_InterDeliveryConfig]
WHERE DeleteUser = 0
AND DestinationName <> '中国'
AND LogisticsID NOT IN ( 1, 95, 97, 100 )
) temp
) temp2
GROUP BY CONVERT(VARCHAR(10), temp2.日期, 23) ,
temp2.线路;