使用distinct时如何限制值 [英] How to limit values when using distinct

问题描述

PHP

  SELECT DISTINCT bk.title AS Title, bk.year AS Year, aut.authorname AS Author, cat.category AS Category
         FROM book bk 

         JOIN book_category bk_cat 
         ON bk_cat.book_id = bk.bookid

         JOIN categories cat 
         ON cat.id = bk_cat.category_id

         JOIN books_authors bk_aut 
         ON bk_aut.book_id = bk.bookid

         JOIN authors aut
         ON aut.id = bk_aut.author_id

         ORDER BY bk.title ASC

我的数据库正在回显以下数据，您可以看到如果书有多个类别，它会多次打印该书.任何人都可以从我的 php 代码中告诉我如何使它与类别列不同.谢谢.

My data base is echoing out the following data you can see that it prints the book out more than once if it has more than one category. from my php code can anyone tell me how i can make it distinct from the category column. Thanks.

推荐答案

仅在您想要的列中实现不同数据的最简单方法是使用 GROUP BY 子句.默认情况下，它会根据列的值对行进行分组，仅显示不同的值，因此，如果您想分组并仅显示不同的标题和类别，则应将查询编写为:

The easiest way to achieve different data only in the columns you want is using GROUP BY clause. By default, it'll group the rows, depending on the value of the column, showing only distinct values so, if you want to group and show only different titles and categories, you should write your query as:

  SELECT bk.title AS Title, bk.year AS Year, aut.authorname AS Author, cat.category AS Category
  FROM book bk 

  JOIN book_category bk_cat 
  ON bk_cat.book_id = bk.bookid

  JOIN categories cat 
  ON cat.id = bk_cat.category_id

  JOIN books_authors bk_aut 
  ON bk_aut.book_id = bk.bookid

  JOIN authors aut
  ON aut.id = bk_aut.author_id

  GROUP BY  bk.title, cat.category
  ORDER BY bk.title ASC

如您所见，没有使用 DISTINCT，但您将获得具有不同标题和类别的所有书籍.您在 GROUP BY 子句中添加的字段越多，您获得的数据就越不同.

As you may see, no DISTINCT is used, but you'll get all books with distincts title and categories. The more fields you added into the GROUP BY clause, the more distinct data you'd get.

同样的，如果你只想按标题列出书籍，你应该只在 GROUP BY 子句中留下 bk.title

Same way, if you only wanted list books by title, you should only leave bk.title in the GROUP BY clause

这篇关于使用distinct时如何限制值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！