sql - 计算另一个表中记录数的查询

Question

我有表merchants:

merchant_id |  merchant_name  | ... |
-------------------------------------
      1     | merchant name 1 | ... |
      2     | merchant name 2 | ... |
      3     | merchant name 3 | ... |

产品表产品:

 product_id |  product_name  | ... |
------------------------------------
      1     | product name 1 | ... |
      2     | product name 2 | ... |
      3     | product name 3 | ... |

还有表likes_products:

 like_id |  product_Id  | user_id |
-----------------------------------
    1    |      101     |   101   |
    2    |      102     |   102   |
    3    |      103     |   101   |

表likes_merchants:

 like_id |  merchants_Id  | user_id |
-----------------------------------
    1    |       107      |   101   |
    2    |       108      |   102   |
    3    |       109      |   101   |

表bookmarks_products:

 bookmark_id |  product_Id  | user_id |
---------------------------------------
       1     |      101     |   101   |
       2     |      102     |   102   |
       3     |      103     |   101   |

我提出一个显示所有产品的请求，计算它们的点赞数。然后他看起来像现在授权产品的用户的用户，如果这个用户有产品标签，并显示true或false:

SELECT P.* , 
  COUNT(L.USER_ID) AS LIKES,
  (B.PRODUCT_ID IS NOT NULL) AS BOOKMARKS,
  (L.PRODUCT_ID IS NOT NULL) AS IS_LIKED
    FROM PRODUCTS AS P
      LEFT JOIN BOOKMARKS_PRODUCTS AS B ON (B.PRODUCT_ID = P.PRODUCT_ID)
      LEFT JOIN LIKES_PRODUCTS AS L ON (L.PRODUCT_ID = P.PRODUCT_ID)
        GROUP BY P.PRODUCT_ID, B.PRODUCT_ID, L.PRODUCT_ID
          ORDER BY P.PRODUCT_ID

我的问题是:1)我无法在书签中查看授权用户喜欢或喜欢的内容。如果任何调用者喜欢某个产品或将其添加为书签，他将发布 true。

2) 我不明白他为什么把点赞数和书签数放在一起考虑。即:如果一个用户点了一个赞，收藏了一个产品，那么点了一个，然后另一个用户收藏了这个产品，那么点赞就增加了，如果他喜欢这个产品，就会翻倍。

请帮助我理解我的问题

我期望收到的:

 product_id |  product_name  | ... | LIKES | BOOKMARKS | IS_LIKED |
-------------------------------------------------------------------
      1     | product name 1 | ... |   1   |   false   |   true   |
      2     | product name 2 | ... |   0   |   true    |   true   |
      3     | product name 3 | ... |   5   |   false   |   false  |

但问题是，如果表中有用户的点赞，表中有用户的书签，它会将它们相加并输出正确的一半点赞。

这是没有 GROUP BY 的情况:

SELECT P.* , 
  (B.PRODUCT_ID IS NOT NULL) AS BOOKMARKS,
  (L.PRODUCT_ID IS NOT NULL) AS IS_LIKED
    FROM PRODUCTS AS P
      LEFT JOIN BOOKMARKS_PRODUCTS AS B ON (B.PRODUCT_ID = P.PRODUCT_ID)
      LEFT JOIN LIKES_PRODUCTS AS L ON (L.PRODUCT_ID = P.PRODUCT_ID)
          ORDER BY P.PRODUCT_ID

 product_id |  product_name  | ... | LIKES | BOOKMARKS | IS_LIKED |
-------------------------------------------------------------------
      1     | product name 1 | ... |   1   |   false   |   true   |
      1     | product name 1 | ... |   1   |   false   |   true   |
      3     | product name 3 | ... |   5   |   false   |   false  |

产品 1 在 likes_products 和 bookmarks_products 两个表中输入了两次

Answer 1

你可以试试下面-

select A.product_id,likes,
case when B.PRODUCT_ID IS NOT NULL then 'true' else 'false' end AS BOOKMARKS,
case when L.PRODUCT_ID IS NOT NULL then 'true' else 'false' end AS IS_LIKED
from
(  
SELECT P.PRODUCT_ID , 
  COUNT(L.USER_ID) AS LIKES,
  FROM PRODUCTS AS P
  LEFT JOIN LIKES_PRODUCTS AS L ON L.PRODUCT_ID = P.PRODUCT_ID
  GROUP BY P.PRODUCT_ID
)A 
LEFT JOIN BOOKMARKS_PRODUCTS AS B ON B.PRODUCT_ID = A.PRODUCT_ID
LEFT JOIN LIKES_PRODUCTS AS L ON L.PRODUCT_ID = A.PRODUCT_ID
order by A.product_id