MySQL GROUP BY 杀死 ORDER BY

Question

我遇到了一个问题，并且已经有一些类似的问题，但它们没有解决我的问题。

里面有几家酒店和照片。图片按编号排序。我想要所有酒店和编号最低的图片。

当我尝试以下查询时，我得到了正确的顺序:

SELECT s25.entry_id AS id, 
s25.value AS title, 
s35.file AS picture, 
s86.value AS picture_sort 
FROM sym_entries_data_25 AS s25 
LEFT JOIN sym_entries_data_34 AS s34 ON (s25.entry_id = s34.relation_id)     
LEFT JOIN sym_entries_data_35 AS s35 ON (s34.entry_id = s35.entry_id) 
LEFT JOIN sym_entries_data_86 AS s86 ON (s34.entry_id = s86.entry_id) 
ORDER BY s86.value

我会得到数组

[0] => Array
    (
        [id] => 243
        [title] => Hotel
        [picture] => louis2.jpg
        [picture_sort] => 1
    )

[1] => Array
    (
        [id] => 243
        [title] => Hotel
        [picture] => louis1.jpg
        [picture_sort] => 2
    )

[2] => Array
    (
        [id] => 243
        [title] => Hotel
        [picture] => louis3.jpg
        [picture_sort] => 3
    )

...等等

但我只想要每家酒店一个结果，所以我尝试了 GROUP BY:

SELECT s25.entry_id AS id, 
s25.value AS title, 
s35.file AS picture, 
s86.value AS picture_sort 
FROM sym_entries_data_25 AS s25 
LEFT JOIN sym_entries_data_34 AS s34 ON (s25.entry_id = s34.relation_id)     
LEFT JOIN sym_entries_data_35 AS s35 ON (s34.entry_id = s35.entry_id) 
LEFT JOIN sym_entries_data_86 AS s86 ON (s34.entry_id = s86.entry_id) 
GROUP BY s25.value
ORDER BY s86.value

然后我只得到一个结果，而是一个随机结果，而不是第一个结果:

[0] => Array
    (
        [id] => 243
        [title] => Hotel
        [picture] => louis3.jpg
        [picture_sort] => 3
    )

这可能是什么问题？

Answer 1

当您执行group by时，您无法保证不作为分组依据的列的值。例如，如果您有一个包含 a 列和 b 列的表，以及如下数据:

a | b
-----
1 | 2
1 | 3

如果您select * from table group by a，则可以获得 2 或 3 作为 b 的值。

如果你只想要酒店的最高结果，你需要做的不是group by，而是取picture_sort等于min(picture_sort)的结果