sql 话语优化挑战_Sql Server

sql 语句优化挑战

本帖最后由 thesum 于 2014-03-20 20:48:40 编辑

INSERT INTO master.dbo.[xl]
            (num6,
             SCD,
             BCD,
             Sscore,
             Bscore)
SELECT num5,
       SCD,
       b.CollectDate,
       Sscore,
       b.SwapScore
FROM   master.dbo.[xl] a,
       new.dbo.RecordInsure b
WHERE  num5 > 0
       AND num5 = b.TargetUserID
       AND b.CollectDate < SCD
       AND b.TradeType = 3
       AND (SELECT TOP 1 TargetUserID
            FROM   new.dbo.RecordInsure
            WHERE  CollectDate < a.SCD
                   AND TradeType = 3
                   AND ( TargetUserID = a.num5
                          OR SourceUserID = a.num5 )
            ORDER  BY CollectDate DESC) = a.num5
       AND (SELECT TOP 1 CollectDate
            FROM   new.dbo.RecordInsure
            WHERE  CollectDate < a.SCD
                   AND TradeType = 3
                   AND ( TargetUserID = a.num5
                          OR SourceUserID = a.num5 )
            ORDER  BY CollectDate DESC) = b.CollectDate
------解决方案--------------------
试试这个：

INSERT INTO master.dbo.[xl]
            (num6,
             SCD,
             BCD,
             Sscore,
             Bscore)
SELECT num5,
       SCD,
       b.CollectDate,
       Sscore,
       b.SwapScore
FROM   master.dbo.[xl] a
inner join 
(
    select TargetUserID,max(CollectDate) CollectDate
    from new.dbo.RecordInsure
    where TradeType = 3
    group by TargetUserID
)b
 on num5 = b.TargetUserID and b.CollectDate < a.SCD
WHERE  num5 > 0