子表

1----

WITH D AS(SELECT E.ROW_ID,E.BRAND,F.REVIEW_DATE FROM T_NWP_TRADEZONE_INFO E
  JOIN T_NWP_TRADEZONE_REVIEW F
    ON E.ROW_ID = F.FK_ROW_ID
 WHERE F.ROW_ID = (
     SELECT TOP 1 G.ROW_ID FROM T_NWP_TRADEZONE_REVIEW G WHERE E.ROW_ID = G.FK_ROW_ID ORDER BY G.REVIEW_DATE DESC
 )
 UNION ALL
 
SELECT D.ROW_ID,D.BRAND,CONVERT(NVARCHAR(10), H.CREATE_TIME,20) AS REVIEW_DATE FROM (
    SELECT A.ROW_ID,A.BRAND,B.REVIEW_DATE
      FROM T_NWP_TRADEZONE_INFO A
 LEFT JOIN T_NWP_TRADEZONE_REVIEW B
        ON A.ROW_ID = B.FK_ROW_ID
     WHERE B.REVIEW_DATE IS NULL
) D ,H_NWP_TRADE_PLAN_REPORT H
  WHERE D.ROW_ID = H.FK_ROW_ID AND
  H.ROW_ID = (
     SELECT TOP 1 I.ROW_ID FROM H_NWP_TRADE_PLAN_REPORT I WHERE I.FK_ROW_ID = D.ROW_ID ORDER BY I.CREATE_TIME
 ))
 SELECT * FROM D

 

2----

WITH D(
    ROW_ID,
    BRAND,
    REVIEW_DATE
)AS(
    SELECT A.ROW_ID,A.BRAND,B.REVIEW_DATE
      FROM T_NWP_TRADEZONE_INFO A
 LEFT JOIN T_NWP_TRADEZONE_REVIEW B
        ON A.ROW_ID = B.FK_ROW_ID
     WHERE B.REVIEW_DATE IS NULL
),
X AS(

SELECT E.ROW_ID,E.BRAND,F.REVIEW_DATE FROM T_NWP_TRADEZONE_INFO E
  JOIN T_NWP_TRADEZONE_REVIEW F
    ON E.ROW_ID = F.FK_ROW_ID
 WHERE F.ROW_ID = (
     SELECT TOP 1 G.ROW_ID FROM T_NWP_TRADEZONE_REVIEW G WHERE E.ROW_ID = G.FK_ROW_ID ORDER BY G.REVIEW_DATE DESC
 )
 UNION ALL
 
SELECT D.ROW_ID,D.BRAND,CONVERT(NVARCHAR(10), H.CREATE_TIME,20) AS REVIEW_DATE FROM D
  JOIN H_NWP_TRADE_PLAN_REPORT H
    ON D.ROW_ID = H.FK_ROW_ID
 WHERE H.ROW_ID = (
     SELECT TOP 1 I.ROW_ID FROM H_NWP_TRADE_PLAN_REPORT I WHERE I.FK_ROW_ID = D.ROW_ID ORDER BY I.CREATE_TIME
 ))
 SELECT * FROM X