MySQL INNER JOIN 查询存在问题，其中包含子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15516941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Having issue with MySQL INNER JOIN query having sub-query in it
提问by user1995997
I have two tables, category and images. Here, Category.ID == Images.Category
我有两个表，类别和图像。这里，Category.ID == Images.Category
Category
类别
-----------------------
| ID | parent | name |
-----------------------
| 1 | 1 | foo |
| 2 | 1 | bar |
| 3 | 2 | lorem |
-----------------------
Images
图片
--------------------------------------
| ID | category | url |
--------------------------------------
| 1 | 1 | foo.jpg |
| 2 | 2 | bar.jpg |
| 3 | 1 | foo2.jpg |
--------------------------------------
I tried MySQL Query
我试过 MySQL 查询
SELECT *
FROM `category`
INNER JOIN
(SELECT MAX(ID) , url, category FROM `images` GROUP BY `category`)
AS a ON category.ID = a.category
WHERE `parent` = '1'
Which Results in
结果是
-------------------------------------------
| ID | parent | name | url | max(ID) |
-------------------------------------------
| 1 | 1 | foo | foo.jpg | 3 |
| 2 | 1 | bar | bar.jpg | 2 |
-------------------------------------------
The Problem is
问题是
I want url of last added row in here, but as in first row, Instead of url = foo2.jpg and max(ID) = 3, it results in foo.jpg. I can't figure out the problem in query.
我想要最后添加行的 url 在这里，但在第一行，而不是 url = foo2.jpg 和 max(ID) = 3，它导致 foo.jpg。我无法弄清楚查询中的问题。
I use max(ID) for getting last row, which gives correct last row for max(ID) but not appropriate url column.
我使用 max(ID) 来获取最后一行，它为 max(ID) 提供了正确的最后一行，但不是适当的 url 列。
回答by Mahmoud Gamal
Try this instead:
试试这个：
SELECT *
FROM `category` AS c
INNER JOIN images AS i ON i.category = c.id
INNER JOIN
(
SELECT category, MAX(ID) AS MAXId
FROM `images`
GROUP BY `category`
)AS a ON i.category = a.category
AND i.ID = a.MaxID
WHERE c.`parent` = '1';
SQL Fiddle Demo
SQL 小提琴演示
The problem is that, you were GROUP BY categoryinside the subquery and select MAX(ID) , url, categorywhich were not included in an aggregate function nor in the GROUP BYclause, so MySQL picks up an arbitrary value for these columns. Thats why you were getting in consistent results.
问题是，您GROUP BY category在子查询和 select 中MAX(ID) , url, category，它们既没有包含在聚合函数中，也没有包含在GROUP BY子句中，因此 MySQL 会为这些列选择一个任意值。这就是为什么你得到一致的结果。
To solve this, JOINthe two tables categoryand imagesnormally, then add an extra join between the table imagesand a subquery which compute the MAX(id)with GROUP BY categoryfor the same table images. Then join this subquery with the table imageson the join condition that the max id= to the original id.
为了解决这个问题，JOIN这两个表category和images正常，然后添加一个额外的表之间的连接images和子查询其计算MAX(id)与GROUP BY category同一个表images。然后将这个子查询与表images的连接条件连接起来，即 max id= 到原始id.
This will give you only those image details for the last id.
这将只为您提供最后一个id.
回答by User 1531343
In a Shorter way
以更短的方式
SELECT c.id,c.parent,c.name, MAX(c.ID) , url, category
FROM `category` c
INNER JOIN Images i on c.id=i.id and
c.id=(select max(id) from category)
I think this query will work for you..tested with sql fiddle...with this link. http://sqlfiddle.com/#!2/5fe63/36
我认为这个查询对你有用……用 sql fiddle 测试过……用这个链接。 http://sqlfiddle.com/#!2/5fe63/36
let me know if solved
如果解决了请告诉我
