Sql查询 求优化,该如何解决

Quote: 引用:

单一表的聚合，做好索引即可。 看看执行计划 Ctrl+L 

SELECT  CompanyId ,
        Account ,
        BigPnr ,
        Pnr ,
        Dep ,
        Arr ,
        AirCode ,
        Flight ,
        Flights ,
        TripType ,
        Cabin ,
        DepTime ,
        SiteId ,
        LogTime ,
        AirPolicyNum ,
        PolicyNum ,
        BackNum ,
        MaxC ,
        MinC ,
        C
FROM    ( SELECT    CompanyId ,
                    Account ,
                    BigPnr ,
                    Pnr ,
                    Dep ,
                    Arr ,
                    AirCode ,
                    Flight ,
                    Flights ,
                    TripType ,
                    Cabin ,
                    DepTime ,
                    SiteId ,
                    LogTime ,
                    AirPolicyNum ,
                    PolicyNum ,
                    BackNum ,
                    MAX(CabinPrice) AS MaxC ,
                    MIN(CabinPrice) AS MinC ,
                    MAX(CabinPrice) - MIN(CabinPrice) AS C
          FROM      ( SELECT    CompanyId ,
                                Account ,
                                BigPnr ,
                                Pnr ,
                                Dep ,
                                Arr ,
                                AirCode ,
                                Flight ,
                                Flights ,
                                TripType ,
                                Cabin ,
                                DepTime ,
                                SiteId ,
                                SUBSTRING(CONVERT(VARCHAR, LogTime, 120), 0,
                                          17) AS LogTime ,
                                AirPolicyNum ,
                                PolicyNum ,
                                BackNum ,
                                CabinPrice
                      FROM      dbo.B2BPriceLog
                      WHERE     ( AirCode = @AirCode
                                  OR @AirCode = ''
                                )
                                AND LogTime < @eLogtime
                                AND LogTime > @sLogtime
                    ) AS T1
          GROUP BY  CompanyId ,
                    Account ,
                    BigPnr ,
                    Pnr ,
                    Dep ,
                    Arr ,
                    AirCode ,
                    Flight ,
                    Flights ,
                    TripType ,
                    Cabin ,
                    DepTime ,
                    LogTime ,
                    AirPolicyNum ,
                    PolicyNum ,
                    BackNum ,
                    SiteId
        ) AS T
WHERE   ( C <> 0 )
        AND ( MinC <> 0 )

麻烦再请教，现在需要按BigPnr，Dep，Arr三个字段Group By ，然后剔除掉Group By后记录数大于2条的数据，应该怎么写呢。想了很久，没想到合适的