如何将两个SQL查询的结果合并为一个查询 - How to combine the results of two SQL queries into one

我有两个复杂的查询，我想合并。

第一个查询返回这些列:

BOOKID
BOOKNAME
NUM_READ
DID_I_READ_IT

第二个查询返回这些列:

BOOKID
BOOKNAME
NUM_LIKES
DID_I_LIKE_IT

我想将这些合并成一个结果，以获得所有列

BOOKID
BOOKNAME
NUM_ADDED
DID_I_ADD_IT
NUM_LIKEs
DID_I_LIKE_IT

书:

| ID | BOOKNAME |
|----|----------|
|  1 |    Book1 |
|  2 |    Book2 |
|  3 |    Book3 |
|  4 |    Book4 |
|  5 |    Book5 |

USERS_BOOKS:

| ID | USERID | BOOKID | STATUS |
|----|--------|--------|--------|
|  1 |    001 |      1 |   Read |
|  2 |    001 |      2 |   Read |
|  3 |    001 |      3 |  Added |
|  4 |    002 |      1 |  Added |
|  5 |    002 |      5 |  Added |
|  6 |    003 |      2 |   Read |
|  7 |    004 |      4 |   Read |

从这个sqlfiddle中，我可以查询图书列表，阅读它们的人数以及我(用户001)是否阅读了它们。

     SELECT a.ID, a.BOOKNAME, a.NUM_READ, 
            CASE WHEN b.BOOKID IS NULL THEN 'No' ELSE 'Yes' END AS DID_I_READ_IT
       FROM (
             select BOOKS.ID, BOOKS.BOOKNAME, 
                    SUM(CASE WHEN USERS_BOOKS.STATUS='Read' THEN 1 ELSE 0 END) AS NUM_READ
               from BOOKS
          LEFT JOIN USERS_BOOKS ON USERS_BOOKS.BOOKID = BOOKS.ID 
           GROUP BY BOOKS.ID
            ) AS a
  LEFT JOIN (
           select DISTINCT USERID, 
                           BOOKID
             FROM USERS_BOOKS
            WHERE STATUS = 'Read'
              AND USERID = '001'
            ) AS b ON a.ID = b.BOOKID

结果:

| ID | BOOKNAME| NUM_READ | DID_I_READ_IT|
|----|---------|----------|--------------|
|  1 |  BOOK1  |    1     |      Yes     |
|  2 |  BOOK2  |    2     |      Yes     |
|  3 |  BOOK3  |    0     |      No      |
|  4 |  BOOK4  |    1     |      No      |
|  5 |  BOOK5  |    0     |      No      |

还有一个表用于存储图书的like:

| ID | USERID | BOOKID | STATUS |
|----|--------|--------|--------|
|  1 |    001 |      1 |   Yes  |   //USER 001 likes BOOK 1
|  2 |    001 |      2 |   No   |   //USER 001 does not like BOOK 2
|  3 |    001 |      3 |   Yes  |
|  4 |    002 |      1 |   Yes  |   //USER 002 likes BOOK 1
|  5 |    002 |      5 |   No   |
|  6 |    003 |      2 |   Yes  |
|  7 |    004 |      4 |   No   |

从这个sqlfiddle中，我可以查询图书列表，喜欢它们的人数以及我(用户001)是否喜欢它们。

结果:

| ID | BOOKNAME| NUM_LIKES | DID_I_LIKE_IT|
|----|---------|-----------|--------------|
|  1 |  BOOK1  |     2     |      Yes     |
|  2 |  BOOK2  |     1     |      No      |
|  3 |  BOOK3  |     1     |      Yes     |
|  4 |  BOOK4  |     0     |      No      |
|  5 |  BOOK5  |     0     |      No      |

我想把这两个查询合并成一个，所以我想要一个书的列表，阅读它们的人数，喜欢它们的人数，我是否读过它们，我是否喜欢它们。

预期的结果:

| ID | BOOKNAME| NUM_READ | DID_I_READ_IT| NUM_LIKES | DID_I_LIKE_IT|
|----|---------|----------|--------------|-----------|--------------|
|  1 |  BOOK1  |    1     |      Yes     |     2     |      Yes     |
|  2 |  BOOK2  |    2     |      Yes     |     1     |      No      |
|  3 |  BOOK3  |    0     |      No      |     1     |      Yes     |
|  4 |  BOOK4  |    1     |      No      |     0     |      No      |
|  5 |  BOOK5  |    0     |      No      |     0     |      No      |

当然，当有人喜欢一本没有人读或没有人添加到他们的书单中的书，或者一本从未被喜欢但被很多人阅读的书时，将会有空值。

这是一种使用条件聚合制定查询的方法。它使用子查询来遵循原始查询的逻辑:

 SELECT b.ID, b.BOOKNAME, r.NUM_READ, 
        (CASE WHEN r.IReadItFlag > 0 THEN 'Yes' ELSE 'No' END) AS DID_I_READ_IT,
        l.NUM_LIKED,
        (CASE WHEN l.ILikedItFlag > 0 THEN 'Yes' ELSE 'No' END) AS DID_I_LIKE_IT
 FROM books b LEFT OUTER JOIN
      (SELECT ub.bookid, SUM(ub.status = 'Read') AS NUM_READ,
              SUM(ub.status = 'Read' AND ub.userid = '001') as IReadItFlag
       FROM users_books ub
       GROUP BY ub.bookid
      ) r
      on b.id = r.bookid LEFT OUTER JOIN
      (SELECT ul.bookid, SUM(ul.liked = 'Yes') AS NUM_LIKED,
              SUM(ul.liked = 'Yes' AND ul.userid = '001') as ILikedItFlag
       FROM users_likes ul
       GROUP BY ul.bookid
      ) l
      on b.id = l.bookid;