mysql - 具有内连接查询的唯一结果集

Question

SELECT 
    * 
FROM `catalog_webdesign_products` t 
INNER JOIN tbl_member_registration t1 ont.userid=t1.fld_loginid 
WHERE 1 
    AND t1.fld_member_category_level<9 
    AND t.product_img IS NOT NULL  
ORDER BY RAND() LIMIT 5

在此查询中，我需要具有唯一用户 ID 的行。我也尝试过“按用户 ID 分组”，但结果集中出现了带有空值的 product_img。

Answer 1

是的，您可以使用GROUP BY userid。但是你的查询在这里看起来是错误的

   INNER JOIN tbl_member_registration t1 on t.userid=t1.fld_loginid
                                           ^------------------------space here

试试这个

  SELECT 
  * 
  FROM `catalog_webdesign_products` t 
  INNER JOIN tbl_member_registration t1 on t.userid=t1.fld_loginid 
  WHERE  t1.fld_member_category_level<9 
  AND t.product_img IS NOT NULL  
  group by userid
  ORDER BY RAND() LIMIT 5

编辑:

 SELECT 
  * 
  FROM (select * from `catalog_webdesign_products` where product_img IS NOT NULL) t 
  INNER JOIN tbl_member_registration t1 on t.userid=t1.fld_loginid 
  WHERE  t1.fld_member_category_level<9 
  group by t.userid
  ORDER BY RAND() LIMIT 5