mysql - Sql 多次返回行

Question

我有以下sql:

select `items`.`id`, `items`.`item_url`, `items`.`title`, `items`.`description`, `items`.`item_image_name`, `items`.`downloads`, `items`.`likes`, `items`.`date` 
from `items` left join `categories` as `c` on `c`.`id` = `items`.`category_id` 
 left join `item_tags` as `it` on `it`.`item_id` = `items`.`id` 
 inner join `tags` as `t` on `t`.`id` = `it`.`tag_id` 
 where (`items`.`title` LIKE '%psd%' or `c`.`name` LIKE '%psd%' or `t`.`name` LIKE '%psd%') 
 order by `items`.`date` desc limit 12

但由于某种原因，某些行在项目表中重复多次。有没有一种方法可以从项目中获取结果而不重复相同的项目 ID。

Answer 1

如果您想避免重复数据，可以尝试使用DISTINCT。像这样的事情:

select DISTINCT `items`.`id`, `items`.`item_url`, `items`.`title`, `items`.`description`, `items`.`item_image_name`, `items`.`downloads`, `items`.`likes`, `items`.`date` 
from `items` left join `categories` as `c` on `c`.`id` = `items`.`category_id` 
 left join `item_tags` as `it` on `it`.`item_id` = `items`.`id` 
 inner join `tags` as `t` on `t`.`id` = `it`.`tag_id` 
 where (`items`.`title` LIKE '%psd%' or `c`.`name` LIKE '%psd%' or `t`.`name` LIKE '%psd%') 
 order by `items`.`date` desc limit 12

或者您也可以使用GROUP BY

select `items`.`id`, `items`.`item_url`, `items`.`title`, `items`.`description`, `items`.`item_image_name`, `items`.`downloads`, `items`.`likes`, `items`.`date` 
    from `items` left join `categories` as `c` on `c`.`id` = `items`.`category_id` 
     left join `item_tags` as `it` on `it`.`item_id` = `items`.`id` 
     inner join `tags` as `t` on `t`.`id` = `it`.`tag_id` 
     where (`items`.`title` LIKE '%psd%' or `c`.`name` LIKE '%psd%' or `t`.`name` LIKE '%psd%') 
     group by `items`.`id`, `items`.`item_url`, `items`.`title`, `items`.`description`, `items`.`item_image_name`, `items`.`downloads`, `items`.`likes`, `items`.`date` 
     order by `items`.`date` desc limit 12