mysql - 我怎样才能加入这两个复杂的查询？

Question

我有两个查询，它们非常复杂，所以我似乎不知道如何加入它们。

我想从两个查询中找到 Q1.notAnyFewID = Q2.FBID 时的结果集

问题 1:

SELECT DISTINCT notifications.`receiver` AS notAnyFewID
FROM notifications
JOIN 
(SELECT notifications.`ref` AS notRef, notifications.`receiver` AS recI
    FROM notifications
    WHERE notifications.`ref`='tooFewLandings') AS c
ON notifications.`receiver`=c.recI
WHERE notifications.`receiver`!=c.recI

问题 2:

SELECT DISTINCT R2PProfiles.id AS r2pID, R2PProfiles.`facebookID` AS FBID
FROM R2PProfiles
LEFT JOIN
      (SELECT COUNT(*) AS Landings, R2PProfiles.facebookID, R2PProfiles.id
            FROM pageTrack         
            JOIN (R2PProfiles)
        ON (pageTrack.inputRefNum = R2PProfiles.id)
WHERE pageTrack.ref='getProfile-Land' AND R2PProfiles.published=2 AND R2PProfiles.`createTime`< NOW()- INTERVAL 24 HOUR GROUP BY R2PProfiles.id) AS h
USING (id) WHERE (Landings < 20)

当尝试组合它们时，我似乎总是在加入或子选择或“使用”或在何处以及如何在正确的地方获得新的方面搞砸了。

将一个查询与另一个查询的结果进行比较的最佳方法是什么？

Answer 1

只需将两个查询作为子查询放入JOIN

SELECT notAnyFewID, r2pID
FROM (SELECT DISTINCT notifications.`receiver` AS notAnyFewID
        FROM notifications
        JOIN 
        (SELECT notifications.`ref` AS notRef, notifications.`receiver` AS recI
            FROM notifications
            WHERE notifications.`ref`='tooFewLandings') AS c
        ON notifications.`receiver`=c.recI
        WHERE notifications.`receiver`!=c.recI) AS q1
JOIN (SELECT DISTINCT R2PProfiles.id AS r2pID, R2PProfiles.`facebookID` AS FBID
        FROM R2PProfiles
        LEFT JOIN
              (SELECT COUNT(*) AS Landings, R2PProfiles.facebookID, R2PProfiles.id
                    FROM pageTrack         
                    JOIN (R2PProfiles)
                ON (pageTrack.inputRefNum = R2PProfiles.id)
        WHERE pageTrack.ref='getProfile-Land' AND R2PProfiles.published=2 AND R2PProfiles.`createTime`< NOW()- INTERVAL 24 HOUR GROUP BY R2PProfiles.id) AS h
        USING (id) WHERE (Landings < 20)) AS q2 
ON q1.notAnyFewID = q2.FBID