MYSQL GROUP BY和ORDER BY不能像预期的那样一起工作

Question

我将尝试用两个案例来解释我的问题：

1. 当我在查询中仅使用ORDER BY子句时：请参见第一张图片。

   

2. 当我同时使用GROUP BY和ORDER BY子句时：请参见第二张图片

   

我的要求是每个酒店只获取一条记录，不重复，并且第二个排序应该是最低的酒店价格应该在顶部；

我的意思是，我需要将third_party_rate作为1041拉取的记录。

您可以查看以下以文本格式显示的MySQL代码：

SELECT h.hotel_id AS id,h.hotel_city AS city,h.hotel_name AS hotelname,h.hotel_star AS hotelcat,hwd.double_spl_rate, hwd.extra_bed_spl_rate,hwd.meal_plan_spl,hwd.third_party_rate,hwd.third_party_extra_bed, hwd.third_party_meal_plan,hwd.room_category,hrd.hotel_rate_from,hrd.hotel_rate_to FROM hotels_list AS h 
INNER JOIN hotel_rate_detail AS hrd ON h.hotel_id = hrd.hotels_id 
INNER JOIN hotel_week_days AS hwd ON hrd.hotel_id = hwd.h_id 
WHERE ( ('2015-07-02' BETWEEN hrd.hotel_rate_from AND hrd.hotel_rate_to) OR ('2015-07-03' BETWEEN hrd.hotel_rate_from AND hrd.hotel_rate_to) ) AND (h.hotel_city = '1')
AND (hwd.double_spl_rate != 0 OR hwd.third_party_rate != 0) AND (h.hotel_star <= '2') 
AND h.hotel_id = 364
GROUP BY h.hotel_id ORDER BY hwd.double_spl_rate,hwd.third_party_rate ASC;

请注意，上面的内容仅使用id = 364来展示结果，但实际上还有许多其他重复的旅馆ID也存在这种情况，因此我需要一般性的查询语句，不仅适用于id = 364，而且适用于其他ID，这样我就可以用单个查询获得完整的结果。

- ashokostech

---

我不知道“不协同工作”是MySQL语言中的一个功能。你使用的是哪个版本？ - Tim Biegeleisen

请勿将查询和结果作为截图发布，请将它们粘贴为文本。标题不要使用大写字母。 - Sami Kuhmonen

Answer 1

MySQL允许这些愚蠢的查询，其中假定GROUP BY包含所有列，即使您没有指定它们。如果只有一个结果，则首先进行分组，因此排序无效。

您需要使用MIN(third_party_rate)来获取该列的最低值。

- Sami Kuhmonen

---

我甚至会更进一步 - 对我来说，MySQL的这个特性不仅愚蠢，而且是错误和危险的，因为它会导致意外结果。 - Frank Schmitt

@FrankSchmitt 我完全同意。随机选择行而不给出错误消息非常糟糕。但这就是MySQL的情况，用户应该预料到这一点。不幸的是，没有人警告他们。 - Sami Kuhmonen

Answer 2

在SQL中，分组是先进行的，然后才进行排序。因此，在这种情况下，您可以首先对记录进行order，然后使用特定列对结果进行分组，它将返回您想要的记录。您可以像下面示例那样替换查询语句：

e.g

select * from 
  (select * from table_name order by column_name Asc) 
  as t group by t.column_name_for_group

我认为以下内容可以帮助您实现记录：

Select * from (SELECT h.hotel_id AS id,h.hotel_city AS city,h.hotel_name AS hotelname,h.hotel_star AS hotelcat,hwd.double_spl_rate, hwd.extra_bed_spl_rate,hwd.meal_plan_spl,hwd.third_party_rate,hwd.third_party_extra_bed, hwd.third_party_meal_plan,hwd.room_category,hrd.hotel_rate_from,hrd.hotel_rate_to FROM hotels_list AS h 
INNER JOIN hotel_rate_detail AS hrd ON h.hotel_id = hrd.hotels_id 
INNER JOIN hotel_week_days AS hwd ON hrd.hotel_id = hwd.h_id 
WHERE ( ('2015-07-02' BETWEEN hrd.hotel_rate_from AND hrd.hotel_rate_to) OR ('2015-07-03' BETWEEN hrd.hotel_rate_from AND hrd.hotel_rate_to) ) AND (h.hotel_city = '1')
AND (hwd.double_spl_rate != 0 OR hwd.third_party_rate != 0) AND (h.hotel_star <= '2') 
AND h.hotel_id = 364
ORDER BY hwd.double_spl_rate,hwd.third_party_rate ASC) as result_table GROUP BY result_table.id;

- Vishal JAIN

---

“在MySQL中，分组首先完成…” - 这不仅适用于MySQL，而且适用于所有其他关系型数据库管理系统。 - Frank Schmitt

@Vishal 你的代码给我报错：错误代码：1054 在'group statement'中，'result_table.hotel_id'列未知。 - ashokostech

抱歉，我认为你将hotel_id别名为id，你只需要在上面的查询中用result_table.id替换GROUP BY。 - Vishal JAIN

Answer 3

我在php上使用了一个单独的方法来获取id，并将其放入数组中。然后，在另一个sql查询中，我通过查找这些id来搜索数据。

$ids  = array();

$sql = "SELECT pid FROM table_name  WHERE DATE(column_name)='yyyy-mm-dd'  ORDER BY column_name ASC";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {

if (!in_array($row['value'], $ids)) {
    array_push($ids, $row['value']);
}
    }
}

然后

$sql = "SELECT * FROM table_name WHERE id IN (".implode(',', $ids).") ORDER BY column_name ASC";

- Dinesh M