mysql - 根据最低 ID 获取记录

Question

我有一张 table :

table name: page_descriptiondesciption_id (pk), page_id (fk), language_id (fk), name1,                  1,            1,                First page2,                  1,            2,                First page_second_lang3,                  2,            1,                Second page4,                  3,            2,                Third pageGeneraly this table contains datas from pages on different languages.The first row means: First page name is 'First Page' at language 1 - whitch is english right now.I want every page from the page table, and the name at the LOWEST language_id!So something like this:1, 1, 1, First page3, 2, 1, Second Page4, 3, 2, Third page 

我有一个查询:

SELECT * FROM heading_description GROUP BY heading_id ORDER BY heading_id ASC,          language_id ASC

它回馈我想要的。但是如果表格看起来像这样:

table name: page_descriptiondesciption_id, page_id, language_id, name1,             1,       2,           First page_second_lang2,             1,       1,           First page3,             2,       1,           Second page4,             3,       2,           Third page

ORDER BY 不会返回我想要的东西。我试过了。

这里: http://sqlfiddle.com/#!2/de7bd/6/0

在第一行我想看到 language_id 1。

Answer 1

我假设 page_id 和 language_id 是主键

像这样的东西有效(sql fiddle)

select pd.* 
  from page_description pd 
  join (select page_id, 
               min(language_id) as language_id
          from page_description 
         group by page_id) as a
    using (page_id, language_id);