使用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
相关文章