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

debugcn 发表于 Dev

超级摇滚

我有桌子merchants：

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

产品表products：

 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  |

但问题是，如果表中有来自用户的喜欢table, there are bookmarks from users in the，它会将它们汇总并输出正确的一半喜欢。

这是没有发生的情况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

法米

您可以尝试以下-

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。