MySQL加入最大值 [英] MySQL join on max value

问题描述

通过加入entry.id上的下表，我想从food_brands表中提取具有最高type_id的行-因此，我应该以type_id 11940获得下面的前3行

From joining the tables below on the entry.id, I want to extract the rows from the food_brands table which have the highest type_id - so I should be getting the top 3 rows below, with type_id 11940

food_brands

food_brands

        id      brand       type_id 
        15375   cesar       11940
        15374   brunos      11940
        15373   butchers    11940
        15372   bakers      11939
        15371   asda        11939
        15370   aldi        11939

类型

        id      type      quantity      food_id 
        11940   comm      53453         10497
        11939   comm      999           10496

食物

        id      frequency   entry_id 
        10497   twice       12230
        10496   twice       12230
        10495   once        12230

条目

         id     number  
         12230  26  

我对查询的尝试并未过滤掉较低的type.id记录-因此，从下面food_brands中的表记录中，我得到了type_id 11940和11939的表.感谢您为解决此问题提供了帮助！ >

My attempt at the query isn't filtering out the lower type.id records - so from the table records below in food_brands, i'm getting those with type_id 11940 and 11939. Grateful for any help fix this!

SELECT fb.*
                        FROM food_brands fb
                        INNER JOIN types t ON fb.type_id = t.id
                        INNER JOIN
                        (
                            SELECT MAX(id) AS MaxID
                            FROM types
                            GROUP BY id
                        ) t2 ON t.food_id = t2.food_id AND t.id = t2.MaxID
                        INNER JOIN foods f ON t.food_id = f.id
                        INNER JOIN entries e ON f.entry_id = e.id
                        WHERE entries.id = 12230

推荐答案

一个简单的子查询就可以了；

A simple subquery should do it just fine;

SELECT * FROM food_brands WHERE type_id=
  (SELECT MAX(t.id) tid FROM types t
   JOIN foods f ON f.id=t.food_id AND f.entry_id=12230)

要测试的SQLfiddle .

这篇关于MySQL加入最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！