整合问题

 

 

SELECT  RTRIM(LTRIM(WorkName)) WorkName, t.BZKTypeName, t.TestProject,
        t.Factor, BJ, BL
FROM    #tbl t
ORDER BY WorkName, t.BZKTypeName DESC, t.TestProject DESC, t.factor DESC   
 
 
SELECT  *
FROM    dbo.ScoreCode
WHERE   IsDelete = 0

SELECT  RTRIM(LTRIM(WorkName)) WorkName, t.BZKTypeName, t.TestProject,
        t.Factor, BJ, BL, s.Factor AS Factor1
INTO    #t
FROM    #tbl t
        RIGHT JOIN ( SELECT *
                     FROM   dbo.ScoreCode
                     WHERE  IsDelete = 0
                   ) s ON t.BZKTypeName = s.BzkTypeName
                          AND t.TestProject = s.TestProject
ORDER BY WorkName, t.BZKTypeName DESC, t.TestProject DESC, t.factor DESC   
 

 

SELECT  DISTINCT
        WorkName, BZKTypeName, TestProject, Factor1, 0 BJ, 0 BL
INTO    #t1
FROM    #t
WHERE   WorkName IS NOT NULL
 
UPDATE  #t1
SET     BJ = s1.BJ, BL = s1.BL
FROM    #t1 ,
        ( SELECT  DISTINCT
                    WorkName, BZKTypeName, TestProject, Factor, BJ, BL
          FROM      #t
          WHERE     WorkName IS NOT NULL
        ) s1
WHERE   #t1.BZKTypeName = s1.BZKTypeName
        AND #t1.TestProject = s1.TestProject
        AND #t1.Factor1 = s1.Factor
        AND #t1.WorkName = s1.WorkName

SELECT  *
FROM    #t1
 
 
DROP TABLE #t
DROP TABLE #t1