我有2个表，我的目标是只有一个SELECT查询，该查询返回项目名称，与该项目相关的子项目数以及最近添加的子项目是什么。
table_a
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| item_ID | int(11) | NO | PRI | NULL | auto_increment |
| item_name | varchar(300) | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
table_b
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| subitem_ID | int(12) | NO | PRI | NULL | auto_increment |
| subitem_relation | int(12) | NO | | NULL | |
| subitem_name | varchar(300) | NO | | NULL | |
| subitem_date | datetime | NO | | NULL | |
+------------------+--------------+------+-----+---------+----------------+
这是我在table_b中的数据：
+------------+------------------+--------------+---------------------+
| subitem_ID | subitem_relation | subitem_name | subitem_date |
+------------+------------------+--------------+---------------------+
| 1 | 1 | subitem a | 2014-06-09 10:51:20 |
| 2 | 1 | subitem b | 2014-06-10 05:21:52 |
| 3 | 2 | subitem c | 2014-06-16 05:12:10 |
| 4 | 1 | subitem d | 2014-06-18 06:42:14 |
| 5 | 2 | subitem e | 2014-06-23 19:33:08 |
| 6 | 2 | subitem f | 2014-07-02 12:30:25 |
| 7 | 2 | subitem g | 2014-07-04 21:52:40 |
+------------+------------------+--------------+---------------------+
这是我目前拥有的查询：
SELECT
a.item_name,
COUNT(b.subitem_ID) as subitem_no,
b.subitem_name as most_recent_subitem
FROM
table_a as a
INNER JOIN
table_b as b
ON
a.item_ID = b.subitem_relation
GROUP BY
b.subitem_relation;
结果如下：
+-------------+------------+---------------------+
| item_name | subitem_no | most_recent_subitem |
+-------------+------------+---------------------+
| first item | 3 | subitem a |
| second item | 4 | subitem c |
+-------------+------------+---------------------+
这将返回正确数量的子项，但是它将返回第一个相关的子项，而不是最新的子项。我需要以某种方式将table_b中的数据排序为ORDER BY subitem_date DESC，但不确定如何实现。
我正在寻找的结果是：
+-------------+------------+---------------------+
| item_name | subitem_no | most_recent_subitem |
+-------------+------------+---------------------+
| first item | 3 | subitem d |
| second item | 4 | subitem g |
+-------------+------------+---------------------+
最佳答案
我可以使用group_concat() / substring_index()技巧来做到这一点：
SELECT a.item_name, COUNT(b.subitem_ID) as subitem_no,
substring_index(group_cooncat(b.subitem_name order by subitem_date desc
), ',', 1) as most_recent_subitem
FROM table_a a INNER JOIN
table_b b
ON a.item_ID = b.subitem_relation
GROUP BY a.item_ID;
关于mysql - MYSQL查询:订购INNER JOIN数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24610544/