从相关表中获取每组的最大行数 [英] Get max row per group from a related table

问题描述

这是我第一次在这里提问.这对学习非常有帮助.

This is my first time asking a question on here. It has been very helpful with learning.

我正在尝试选择一个表，并仅获取另一表中其特定组具有最大值的行.这是一个非常接近但不完全正确的最佳答案之一(

I am trying to select a table and getting only rows that have a maximum value for its particular group in another table. One of the best answers that is very close but not quite there is this one (SQL Select only rows with Max Value on a Column) but it only relates to a single table. I have found some others with multiple table but not sure how exactly to use it.

我有一张(简化的)桌子

I have a table with (simplified)

prodID, quantity, mach, etc

然后我有一张桌子

prodStatusID, prodID, userID, subStatusID

最后一个带有子状态名称的表

a last table with sub status names

subStatusID, subStatusName

我试图获取一个包含所有第一个表和第二个表的表，但仅包含状态号最大且包含正确状态名称的行.
我现在可能并不重要的其他问题是性能，但是在一到两年之内该问题才真正开始出现.我不知道在select中有一个select是很糟糕的，但是如果我要返回所有产品，那么它将对每个产品进行查询.

I am trying to get a table with all of the first table and the second table but only with the row that has the maximum status number and include the right status name.
My other concern which may not matter now but in a year or two when this thing starts to really fill up is performance. I dont know bad it is to have select inside a select but if I am trying to return all productions then it will be doing a query for every production.

请更加清楚.在第二个表prodStatus中，可能有2个行的prodID为4，但第一个的subStatusID将为1，第二个的subStatusID将为2.userID将不同.我只想找回第二行，因为它具有最高的状态编号，并且我需要与该行关联的userID和statusName.

Just to be clearer. in the second table prodStatus there might be 2 rows with prodID of 4 but the subStatusID for the first one would be 1 and the second one would be 2. The userID will be different. All I want to get back is the second row because it has the highest status number and I need the userID and statusName associated with that row.

我已经搜寻了2天，得到了这个答案，但我只看到1个关于拍卖的信息，但是即使研究了一下，我还是不完全理解.

I have been googling for 2 days to get this answer and I saw 1 about auctions but I just dont fully understand it even after researching it.

推荐答案

您需要创建一个子查询，该子查询将为每个prodID获取最大值subStatusID.

You need to create a subquery which get the maximum value of subStatusID for each prodID.

SELECT  a.*,     -- select only columns that you want to show
        c.*,     -- asterisks means all columns
        d.*
FROM    table1 a
        INNER JOIN
        (
            SELECT prodID, max(subStatusID) maxID
            FROM table2
            GROUP BY prodID
        ) b ON a.prodID = b.prodID 
        INNER JOIN  table2 c
            ON b.prodID = c.prodID AND
                b.maxID = c.subStatusID
        INNER JOIN table3 d
            ON c.subStatusID = d.subStatusID

这篇关于从相关表中获取每组的最大行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！