求大神看看这条SQL怎么优化

[qq_28239481]

SELECT ( po.InsuredUnit + '--' + po.PolicyNo ) AS ProjectName ,
( SELECT COUNT(CustomerID)
FROM Biz_ImportCustomerHis WITH ( NOLOCK )
WHERE ISNULL(CustomerID, '') != 0
AND ( ( ModifyType = 1
AND Status = 1
AND ImportType = 2
)
OR ( ImportType = 1
AND Status = 1
)
)
AND Memo LIKE '%新增%'
AND PolicyNo = po.PolicyNo
AND CreateTime >= '2015-06-25 00:00:00.200'
AND CreateTime < '2016-06-25 00:00:00.200'
) AS InsuredCount ,
( SELECT COUNT(b1.CustomerID)
FROM Biz_HealthMedical b1 WITH ( NOLOCK )
WHERE b1.CustomerID IN ( SELECT CustomerID
FROM Biz_CustomerAccount b2
WHERE b2.PolicyNo = po.PolicyNo )
AND b1.ActivationTime >= '2015-06-25 00:00:00.200'
AND b1.ActivationTime < '2016-06-25 00:00:00.200'
) AS ActiveCount ,
( SELECT COUNT(c3.CustomerID)
FROM Biz_Customer c3 WITH ( NOLOCK )
WHERE c3.HCN IN (
SELECT c1.UserName
FROM Sys_User c1 WITH ( NOLOCK )
WHERE ISNULL(c1.WeiXinID, '') != ''
AND c1.UserName IN (
SELECT c2.HCN
FROM Biz_CustomerAccount c2
WHERE c2.PolicyNo = po.PolicyNo )
AND c1.WeiXinBindTime >= '2015-06-25 00:00:00.200'
AND c1.WeiXinBindTime < '2016-06-25 00:00:00.200' )
) AS WeixinBindCount ,
( SELECT COUNT(d1.CustomerID)
FROM Biz_Customer d1 WITH ( NOLOCK )
WHERE d1.HCN IN (
SELECT d3.HMCardNumber
FROM Biz_Consuming d3
WHERE d3.TransactionType IN ( 0, 3, 8, 9 )
AND d3.AccountID IN (
SELECT d2.AccountID
FROM Biz_CustomerAccount d2
WHERE d2.PolicyNo = po.PolicyNo )
AND d3.CreateTime >= '2015-06-25 00:00:00.200'
AND d3.CreateTime < '2016-06-25 00:00:00.200' )
) AS UserCount ,
( SELECT ISNULL(SUM(e1.Amount), 0)
FROM Biz_Consuming e1 WITH ( NOLOCK )
WHERE e1.TransactionType IN ( 1, 4, 5 )
AND e1.AccountID IN ( SELECT e2.AccountID
FROM Biz_CustomerAccount e2
WHERE e2.PolicyNo = po.PolicyNo )
AND e1.CreateTime >= '2015-06-25 00:00:00.200'
AND e1.CreateTime < '2016-06-25 00:00:00.200'
) AS RechargeAmountSum ,
( SELECT -ISNULL(SUM(f1.Amount), 0)
FROM Biz_Consuming f1 WITH ( NOLOCK )
WHERE f1.TransactionType IN ( 0, 3, 8, 9 )
AND f1.AccountID IN ( SELECT f2.AccountID
FROM Biz_CustomerAccount f2
WHERE f2.PolicyNo = po.PolicyNo )
AND f1.CreateTime >= '2015-06-25 00:00:00.200'
AND f1.CreateTime < '2016-06-25 00:00:00.200'
) AS ConsumAmountSum ,
( SELECT -ISNULL(SUM(g1.Amount), 0)
FROM Biz_Consuming g1 WITH ( NOLOCK )
WHERE g1.TransactionType = 2
AND g1.AccountID IN ( SELECT g2.AccountID
FROM Biz_CustomerAccount g2
WHERE g2.PolicyNo = po.PolicyNo )
AND g1.CreateTime >= '2015-06-25 00:00:00.200'
AND g1.CreateTime < '2016-06-25 00:00:00.200'
) AS ReimburseAmountSum ,
( ( SELECT ISNULL(SUM(e1.Amount), 0)
FROM Biz_Consuming e1 WITH ( NOLOCK )
WHERE e1.TransactionType IN ( 1, 4, 5 )
AND e1.AccountID IN ( SELECT e2.AccountID
FROM Biz_CustomerAccount e2
WHERE e2.PolicyNo = po.PolicyNo )
AND e1.CreateTime >= '2015-06-25 00:00:00'
AND e1.CreateTime < '2016-06-25 00:00:00.200'
)
- ( SELECT -ISNULL(SUM(f1.Amount), 0)
FROM Biz_Consuming f1
WHERE f1.TransactionType IN ( 0, 3, 8, 9 )
AND f1.AccountID IN (
SELECT f2.AccountID
FROM Biz_CustomerAccount f2
WHERE f2.PolicyNo = po.PolicyNo )
AND f1.CreateTime >= '2015-06-25 00:00:00'
AND f1.CreateTime < '2016-06-25 00:00:00.200'
) ) AS AmountSum ,
( SELECT COUNT(i1.Amount)
FROM Biz_Consuming i1 WITH ( NOLOCK )
WHERE i1.TransactionType IN ( 0, 3, 8, 9 )
AND i1.AccountID IN ( SELECT i2.AccountID
FROM Biz_CustomerAccount i2
WHERE i2.PolicyNo = po.PolicyNo )
AND i1.CreateTime >= '2015-06-25 00:00:00.200'
AND i1.CreateTime < '2016-06-25 00:00:00.200'
) AS UseCount
FROM Biz_PolicyInfo po;

[足球不是方的]

可如果这是单次查询，不是视图或者存储过的话，可以先把 SELECT ISNULL(SUM(e1.Amount), 0) FROM Biz_Consuming e1 WITH ( NOLOCK ) WHERE AND e1.AccountID IN ( SELECT e2.AccountID FROM Biz_CustomerAccount e2 WHERE e2.PolicyNo = po.PolicyNo ) AND e1.CreateTime >= '2015-06-25 00:00:00' AND e1.CreateTime < '2016-06-25 00:00:00.200' 这种 调用多次的结果集用select * into 先插入临时表，或者实体表，然后再关联这个表。 普通的sql语句优化的话，用join 替代 in，on后面的条件加索引。

[中国风]

参照以下方式 改：

SELECT 
*
,b.AmountSum
,b.UseCount
FROM    Biz_PolicyInfo po
CROSS APPLY
( SELECT    ISNULL(SUM(CASE WHEN TransactionType IN ( 1, 4, 5 ) THEN i1.Amount ELSE 0 END),0)	-ISNULL(SUM(CASE WHEN i1.TransactionType IN ( 0, 3, 8, 9 ) THEN i1.Amount ELSE 0 END),0)
			,SUM(CASE WHEN i1.TransactionType IN ( 0, 3, 8, 9 ) THEN 1 ELSE 0 END) 
          FROM      Biz_Consuming i1 WITH ( NOLOCK )
          WHERE      i1.AccountID IN ( SELECT    i2.AccountID
                                          FROM      Biz_CustomerAccount i2
                                          WHERE     i2.PolicyNo = po.PolicyNo )
                    AND i1.CreateTime >= '2015-06-25 00:00:00.200'
                    AND i1.CreateTime < '2016-06-25 00:00:00.200'
        ) AS b(AmountSum,UseCount)



		

[中国风]

,ISNULL(b.UseCount,0) AS UseCount--SELECT转换0

[卖水果的net]

SELECT COUNT(b1.CustomerID) FROM Biz_HealthMedical b1 WITH ( NOLOCK ) WHERE b1.CustomerID IN ( SELECT CustomerID FROM Biz_CustomerAccount b2 WHERE b2.PolicyNo = po.PolicyNo ) AND b1.ActivationTime >= '2015-06-25 00:00:00.200' AND b1.ActivationTime < '2016-06-25 00:00:00.200' 调用了多次，看看是否可以变成一次；

[唐诗三百首]

总体思路是把大查询拆分为若干个小查询,分步实现.