MYSQL 连接中的嵌套 Select 语句 [英] Nested Select statement in MYSQL join

问题描述

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.

这篇关于MYSQL 连接中的嵌套 Select 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！