2个子查询的连接实例

表数据的数据结构

SELECT * FROM
(
   SELECT A.*
    ,ROW_NUMBER() OVER
          (
            PARTITION BY A.[WeiduNo]
            ORDER BY  A.[ID] 
          )
          AS rn
    FROM [tbiz_AssScoreWeidu] A
    WHERE  A.ProjectID=65 AND A.PersonID=2430 AND A.RelationID=30
) C
WHERE rn=1

SELECT M.*, N.D_RelationID, N.D_Score FROM
(
  SELECT * FROM
  (
       SELECT A.*
        ,ROW_NUMBER() OVER
              (
                PARTITION BY A.[WeiduNo]
                ORDER BY  A.[ID] 
              )
              AS rn
        FROM [tbiz_AssScoreWeidu] A
        WHERE  A.ProjectID=65 AND A.PersonID=2430 AND A.RelationID=30
    ) C
    WHERE rn=1
) M INNER JOIN
(
    SELECT * FROM
    (
       SELECT WeiduNo D_WeiduNo,RelationID D_RelationID,Score D_Score
        ,ROW_NUMBER() OVER
              (
                PARTITION BY [WeiduNo]
                ORDER BY  [ID] 
              )
              AS rn
        FROM [tbiz_AssScoreWeidu]
        WHERE  ProjectID=65 AND PersonID=2430 AND RelationID=0
    ) D
    WHERE rn=1
)N
ON M.WeiduNo = N.D_WeiduNo AND M.RelationID= N.D_RelationID + 30