消息关闭
    暂无新消息!

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

问题作者 : Duxbury2017-07-12发布
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;

5个回答

︿ 3
 参照以下方式 改:

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)



︿ 3
可如果这是单次查询,不是视图或者存储过的话,可以先把
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后面的条件加索引。
︿ 3
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'

调用了多次,看看是否可以变成一次;