我有三个表*book_category, books和book_pictures*在我的mysql。我想通过修改获得每个图书类别的最新书籍。我用这个查询获取最新的书籍，它工作得很好。
select * from (select * from books ORDER BY amended DESC) AS x GROUP BY book_sub_category_id
但我想加入我的book_pictures表，所以我得到book_pictures url显示图书图像。下面我将向您展示这些表的结构。
book_category
+-------------+-----------------+
|category_id | category_name |
|-------------------------------|
| ca | custom Act |
| ct | Custom Tarif |
|-------------+-----------------+
books
+-------------+-----------------+-----------------+--------------------------+
| book_id | category_id | name | amended |
|-------------------------------|-----------------+--------------------------|
| 01 | ca | custom Act | 01-06-2011 |
| 02 | ca | custom Act | 01-06-2012 |
| 03 | ca | custom Act | 01-06-2013 |
| 04 | ct | custom tarif | 01-07-2011 |
| 05 | ct | custom tarif | 01-07-2012 |
| 06 | ct | custom tarif | 01-07-2013 |
+-------------+-----------------+-----------------+--------------------------+
book_pictures
+-------------+-----------------+-----------------+--------------------------+
| picture_id | book_id | small_url | large_url |
|-------------------------------|-----------------+--------------------------|
| p1 | 01 | url | url |
| p2 | 02 | url | url |
| p3 | 03 | url | url |
| p4 | 04 | url | url |
| p5 | 05 | url | url |
| p6 | 06 | url | url |
+-------------+-----------------+-----------------+--------------------------+
use left join，
我认为这将工作，还没有运行它。
SELECT * FROM (
SELECT b.*,
p.large_url
FROM books b
LEFT JOIN book_pictures p
ON b.book_id = p.book_id
ORDER BY b.amended DESC
) AS x
GROUP BY book_sub_category_id