MYSQL 连接中的嵌套 Select 语句

2022-01-07 00:00:00 join nested mysql greatest-n-per-group

SELECT * FROM A
JOIN B
ON B.ID = A.ID
AND B.Time =   (SELECT max(Time) 
                            FROM B B2
                            WHERE B2.ID = B.ID)

我正在尝试在 MYSQL 中加入这两个表.不要注意如果 ID 是唯一的，那么我就不会尝试这样做.我浓缩了真正的解决方案来画一个简化的图画.我正在尝试在某条记录的最大日期抓取并加入表 B.此过程由 SSIS 包运行，并表示 B2.ID 是未知列.我经常在 MSSQL 中做这样的事情，并且是 MYSQL 的新手.有人有任何指示或想法吗?

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?

推荐答案

我以不同的方式执行此类查询，使用排除连接而不是子查询.您想找到给定 ID 具有最大时间的 B 行；换句话说，没有其他行具有更大的时间和相同的 ID.

I do this type of query differently, with an exclusion join instead 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.

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.:我添加了 greatest-n-per-group 标签.这种类型的 SQL 问题每周都会在 Stack Overflow 上出现，因此您可以按照该标签查看数十个类似问题及其答案.

P.S.: I've added the greatest-n-per-group tag. 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.

相关文章