MYSQL 连接中的嵌套 Select 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7194547/
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
Nested Select statement in MYSQL join
提问by JBone
SELECT * FROM A
JOIN B
ON B.ID = A.ID
AND B.Time = (SELECT max(Time)
FROM B B2
WHERE B2.ID = B.ID)
I am trying to join these two tables in MYSQL. Don't pay attention to that if the ID is unique then I wouldn't be trying to do this. I condensed the real solution to paint a simplified picture. I am trying to grab and join the table B on the max date for a certain record. This procedure is getting run by an SSIS package and is saying B2.ID is an unknown column. I do things like this frequently in MSSQL and am new to MYSQL. Anyone have any pointers or ideas?
我正在尝试在 MYSQL 中加入这两个表。不要注意如果 ID 是唯一的，那么我就不会尝试这样做。我浓缩了真正的解决方案来画一个简化的图画。我正在尝试在某条记录的最大日期抓取并加入表 B。此过程由 SSIS 包运行，并表示 B2.ID 是未知列。我经常在 MSSQL 中做这样的事情，并且是 MYSQL 的新手。任何人有任何指示或想法？
回答by Bill Karwin
I do this type of query differently, with an exclusion joininstead of a subquery. You want to find the rows of B which have the max Time for a given ID; in other words, where no other row has a greater Time and the same ID.
我以不同的方式执行这种类型的查询，使用排除连接而不是子查询。您想找到给定 ID 具有最大时间的 B 行；换句话说，没有其他行具有更大的时间和相同的 ID。
SELECT A.*, B.*
FROM A JOIN B ON B.ID = A.ID
LEFT OUTER JOIN B AS B2 ON B.ID = B2.ID AND B.Time < B2.Time
WHERE B2.ID IS NULL
You can also use a derived table, which should perform better than using a correlated subquery.
您还可以使用派生表，它的性能应该比使用相关子查询更好。
SELECT A.*, B.*
FROM A JOIN B ON B.ID = A.ID
JOIN (SELECT ID, MAX(Time) AS Time FROM B GROUP BY ID) AS B2
ON (B.ID, B.Time) = (B2.ID, B2.Time)
P.S.: I've added the greatest-n-per-grouptag. This type of SQL question comes up every week on Stack Overflow, so you can follow that tag to see dozens of similar questions and their answers.
PS：我已经添加了greatest-n-per-group标签。这种类型的 SQL 问题每周都会在 Stack Overflow 上出现，因此您可以按照该标签查看数十个类似的问题及其答案。
