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?

推荐答案

我用 exclusion join 而不是子查询以不同的方式进行此类查询.您要查找B的行，这些行具有给定ID的最大时间；换句话说，没有其他行具有更大的时间和相同的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标签.每周都会在Stack Overflow上出现这种类型的SQL问题，因此您可以按照该标签查看许多类似的问题及其答案.

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屋！