连接两个查询以返回文章及其类似项

Question

我是Oracle的新手，需要以下方面的帮助:我有以下查询，该查询返回文章列表：

SELECT      LEADERSHIP.LDRSHP_ID, 
            LEADERSHIP.LDRSHP_TITL_DESC, 
            LEADERSHIP.LDRSHP_CNTNT_TXT, 
            LEADERSHIP.LDRSHP_PUBD_DT,
            LEADERSHIP.LDRSHP_ATHR_NM, 
            LEADERSHIP.MEDIA_URL, 
            LEADERSHIP.ARTCL_SRC_ID,
            MEDIA.MEDIA_TYPE_DESCRIP
FROM        RSKLMOBILEB2E.LEADERSHIP_CORNER     LEADERSHIP 
INNER JOIN  RSKLMOBILEB2E.MEDIA_TYPE            MEDIA       ON  LEADERSHIP.MEDIA_TYPE_IDENTIF = MEDIA.MEDIA_TYPE_IDENTIF
WHERE (LEADERSHIP.LDRSHP_ATHR_NM, LEADERSHIP.LDRSHP_PUBD_DT) IN 
(
    SELECT  LDRSHP_ATHR_NM, MAX(LDRSHP_PUBD_DT) 
    FROM    RSKLMOBILEB2E.LEADERSHIP_CORNER 
    GROUP BY LDRSHP_ATHR_NM
)
ORDER BY LEADERSHIP.LDRSHP_PUBD_DT DESC

我还必须为每一篇文章实现总喜欢，通过这个查询我可以得到：

SELECT  LEADERSHIP1.LDRSHP_ID, 
        COUNT(*) TOTAL_LIKES 
FROM    RSKLMOBILEB2E.LEADERSHIP_CORNER     LEADERSHIP1, 
        RSKLMOBILEB2E.LEADERS_LIKES         LIKES1 
WHERE   LEADERSHIP1.LDRSHP_ID = LIKES1.LDRSHP_ID 
group by LEADERSHIP1.LDRSHP_ID

现在，我需要一条SQL语句，将上述两个查询组合在一起，返回所有文章以及每篇文章的总喜欢。

Answer 1

它的工作方式是执行左外部连接2 sql的操作：

SELECT A.LDRSHP_ID
    , A.LDRSHP_TITL_DESC
    , A.LDRSHP_CNTNT_TXT
    , A.LDRSHP_PUBD_DT
    , A.LDRSHP_ATHR_NM
    , A.MEDIA_URL
    , A.ARTCL_SRC_ID
    , A.MEDIA_TYPE_DESCRIP 
    , B.TOTAL_LIKES
FROM
    (
            SELECT LEADERSHIP.LDRSHP_ID
                   , LEADERSHIP.LDRSHP_TITL_DESC
                  , LEADERSHIP.LDRSHP_CNTNT_TXT
                  , LEADERSHIP.LDRSHP_PUBD_DT
                  , LEADERSHIP.LDRSHP_ATHR_NM
                  , LEADERSHIP.MEDIA_URL
                  , LEADERSHIP.ARTCL_SRC_ID
                  , MEDIA.MEDIA_TYPE_DESCRIP 
              FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP 
              INNER JOIN RSKLMOBILEB2E.MEDIA_TYPE MEDIA 
                  ON (LEADERSHIP.MEDIA_TYPE_IDENTIF = MEDIA.MEDIA_TYPE_IDENTIF) 
              WHERE (LEADERSHIP.LDRSHP_ATHR_NM
                  , LEADERSHIP.LDRSHP_PUBD_DT) 
              IN (SELECT LDRSHP_ATHR_NM
                    , MAX(LDRSHP_PUBD_DT) 
                  FROM RSKLMOBILEB2E.LEADERSHIP_CORNER 
                  GROUP BY LDRSHP_ATHR_NM) 
      ) A 
      LEFT OUTER JOIN 
      (
              SELECT LEADERSHIP1.LDRSHP_ID
              , COUNT(*) TOTAL_LIKES 
              FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP1
              , RSKLMOBILEB2E.LEADERS_LIKES LIKES1 
              WHERE LEADERSHIP1.LDRSHP_ID = LIKES1.LDRSHP_ID 
              group by LEADERSHIP1.LDRSHP_ID
      ) B
              ON (A.LDRSHP_ID=B.LDRSHP_ID)
      ORDER BY A.LDRSHP_PUBD_DT DESC 

Answer 2

您也可以在第一个查询中对join表进行likes，并获得count的count。

SELECT  LEADERSHIP.LDRSHP_ID, 
        LEADERSHIP.LDRSHP_TITL_DESC, 
        LEADERSHIP.LDRSHP_CNTNT_TXT, 
        LEADERSHIP.LDRSHP_PUBD_DT,
        LEADERSHIP.LDRSHP_ATHR_NM, 
        LEADERSHIP.MEDIA_URL, 
        LEADERSHIP.ARTCL_SRC_ID,
        MEDIA.MEDIA_TYPE_DESCRIP,
        count(likes.*) total_likes
FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP 
INNER JOIN  RSKLMOBILEB2E.MEDIA_TYPE MEDIA
ON LEADERSHIP.MEDIA_TYPE_IDENTIF = MEDIA.MEDIA_TYPE_IDENTIF
LEFT JOIN RSKLMOBILEB2E.LEADERS_LIKES LIKES
ON LEADERSHIP.LDRSHP_ID = LIKES.LDRSHP_ID
WHERE (LEADERSHIP.LDRSHP_ATHR_NM, LEADERSHIP.LDRSHP_PUBD_DT) IN 
(
SELECT LDRSHP_ATHR_NM, MAX(LDRSHP_PUBD_DT) 
FROM RSKLMOBILEB2E.LEADERSHIP_CORNER 
GROUP BY LDRSHP_ATHR_NM
)
GROUP BY LEADERSHIP.LDRSHP_ID, 
         LEADERSHIP.LDRSHP_TITL_DESC, 
         LEADERSHIP.LDRSHP_CNTNT_TXT, 
         LEADERSHIP.LDRSHP_PUBD_DT,
         LEADERSHIP.LDRSHP_ATHR_NM, 
         LEADERSHIP.MEDIA_URL, 
         LEADERSHIP.ARTCL_SRC_ID,
         MEDIA.MEDIA_TYPE_DESCRIP
ORDER BY LEADERSHIP.LDRSHP_PUBD_DT DESC