MYSQL GROUP BY 和 ORDER BY 没有按预期一起工作 [英] MYSQL GROUP BY and ORDER BY not working together as expected

问题描述

我将尝试用两种情况来解释我的问题:

I will try to explain my question with two cases:

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

1. When I have used only ORDER BY clause in my query: See the 1st image.

当我同时使用 GROUP BY 和 ORDER BY 子句时:见第二张图片

When I have used both GROUP BY and ORDER BY claluse: see 2nd image

我的要求是每家酒店只获得单条记录，没有重复，第二顺序应该是最低的酒店价格应该在TOP;

My requirement is to get only single record for each hotel no duplicacy, and second ordery by should be the lowest hotel price should be on TOP;

我的意思是记录我需要将 third_party_rate 拉为 1041 的记录.

I mean record I need record which will pull third_party_rate as 1041.

可以如下图以文本格式查看MySQL代码:

You can view the MySQL code in text format as shown below:

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 也是如此，以便我通过一个查询获得完整的结果.

Note that I have used only id = 364 on the above context just to show the result but there are many other repeated hotel ids with such cases, so I need a general query which works not only for id = 364 but for other ids as well so that I got complete result with one single query.

推荐答案

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

MySQL will allow these kinds of silly queries, where GROUP BY is assumed to contain all columns even though you don't specify them. Grouping is done first, so ordering does nothing if there is only one result.

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

You need to use MIN(third_party_rate) to get the lowest value for that column.

这篇关于MYSQL GROUP BY 和 ORDER BY 没有按预期一起工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！