mysql - 如何进行查询

Question

查看表有 store_idx、user_idx 等...

我想创建一个查询语句，获取有关用户使用输入的 user_id 值添加书签的商店的信息。

我提出的查询语句是

select A.store_name
     , A.store_img
     , count(B.store_idx) as review_cnt 
  from board.store A 
  Left 
  Join board.review B 
    On A.store_idx is B.store_idx 
 where store_idx is (select A.store_idx from bookmark where user_id = ?)

但是，没有任何结果。

帮帮我..

Answer 1

请使用以下查询:

SELECT store_name
     , store_img
     , SUM(review_cnt) AS review_cnt
  FROM
     ( SELECT DISTINCT A.store_name
            , A.store_img
           , CASE WHEN B.store_idx IS NULL THEN 0 ELSE 1 END AS review_cnt
       FROM bookmark br
       JOIN board.store A 
            ON A.store_idx = br.store_idx
       LEFT 
           JOIN board.review B 
             ON A.store_idx = B.store_idx 
    WHERE br.user_id = ?
     )T