mysql 联合两个查询与顺序

Question

我想合并两个具有不同ORDER BY

的查询

但是另一个查询并不按照它的ORDER BY

我不知道是什么导致了这个错误，但它在我的本地文件中运行良好。但是当我在实时站点上启动它时，第二个查询的行为效果不佳。

问题出在 sub-query table2 上，它应该按 starting_date descending 显示所有记录，但查询没有那样排序......我没有知道是什么原因造成的。

提前致谢

这是我的 mysql 查询。

 SELECT * FROM (
            SELECT 
            events.event_id,
            events.event_name_en,
            events.event_name_ch,
            events.event_description_en,
            events.event_description_ch,
            events.ref,
            events.start_date,
            events.end_date,
            events.venue_id,
            events.organizers_id,
            events.website,
            events.payment_method,
            events.visitors,
            events.display,
            events.booking_form,
            events.map,
            events.lmap,
            events.insurance_offered,
            events.masterhead_img,
            events.icon_img,
            events.event_token,
            events.added_by as event_added_by,
            events.added_date as event_added_date,
            events.updated_by,
            events.updated_date,
            events.active,
            events.allow_ond,
            events.allow_ond_reg,
            events.terms_p,
            events.cancel_p,
            events.no_show_p,
            events.refund_p,
            events.official_desc,
            events.supporting_desc,
            events.participating_desc,
            events.official_title,
            events.supporting_title,
            events.participating_title,
            events.order_number,
            venue.venue_en,
            venue.venue_ch,
            venue.added_by as venue_added_by,
            venue.added_date as venue_added_date,
            venue.updated_by as venue_updated_by,
            venue.updated_date as venue_updated_date,
            countries.country_id,
            countries.country_code,
            countries.country_name

            FROM 
            events 
            INNER JOIN venue
            ON venue.venue_id=events.venue_id
            INNER JOIN countries
            ON countries.country_id=events.country_id
            WHERE events.display = '1' AND events.order_number != ''
            AND events.active = '1' 
            ORDER BY events.order_number ASC) as table1

            UNION ALL

            SELECT * FROM (SELECT 
            events.event_id,
            events.event_name_en,
            events.event_name_ch,
            events.event_description_en,
            events.event_description_ch,
            events.ref,
            events.start_date,
            events.end_date,
            events.venue_id,
            events.organizers_id,
            events.website,
            events.payment_method,
            events.visitors,
            events.display,
            events.booking_form,
            events.map,
            events.lmap,
            events.insurance_offered,
            events.masterhead_img,
            events.icon_img,
            events.event_token,
            events.added_by as event_added_by,
            events.added_date as event_added_date,
            events.updated_by,
            events.updated_date,
            events.active,
            events.allow_ond,
            events.allow_ond_reg,
            events.terms_p,
            events.cancel_p,
            events.no_show_p,
            events.refund_p,
            events.official_desc,
            events.supporting_desc,
            events.participating_desc,
            events.official_title,
            events.supporting_title,
            events.participating_title,
            events.order_number,
            venue.venue_en,
            venue.venue_ch,
            venue.added_by as venue_added_by,
            venue.added_date as venue_added_date,
            venue.updated_by as venue_updated_by,
            venue.updated_date as venue_updated_date,
            countries.country_id,
            countries.country_code,
            countries.country_name

            FROM 
            events 
            INNER JOIN venue
            ON venue.venue_id=events.venue_id
            INNER JOIN countries
            ON countries.country_id=events.country_id
            WHERE events.display = '1' AND events.order_number = ''
            AND events.active = '1' 
            ORDER BY events.start_date DESC) as table2

结果:

前两行不错。 table1 子查询的结果

但是当您查看第三行时……starting_date desc 的顺序不正确……第三行应该是 world engineer blah... ..

Answer 1

来自 MySQL 手册页

http://dev.mysql.com/doc/refman/5.7/en/union.html

但是，对单个 SELECT 语句使用 ORDER BY 并不意味着行在最终结果中的显示顺序，因为默认情况下 UNION 会生成一组无序的行。因此，在此上下文中使用 ORDER BY 通常与 LIMIT 结合使用，因此它用于确定要为 SELECT 检索的所选行的子集，即使它不一定影响这些行在最终的 UNION 结果。如果在 SELECT 中出现没有 LIMIT 的 ORDER BY，它会被优化掉，因为它无论如何都不会起作用。

我认为你可以通过使用 ORDER BY 子句来做你想做的事:-

SELECT events.event_id,
        events.event_name_en,
        events.event_name_ch,
        events.event_description_en,
        events.event_description_ch,
        events.ref,
        events.start_date,
        events.end_date,
        events.venue_id,
        events.organizers_id,
        events.website,
        events.payment_method,
        events.visitors,
        events.display,
        events.booking_form,
        events.map,
        events.lmap,
        events.insurance_offered,
        events.masterhead_img,
        events.icon_img,
        events.event_token,
        events.added_by as event_added_by,
        events.added_date as event_added_date,
        events.updated_by,
        events.updated_date,
        events.active,
        events.allow_ond,
        events.allow_ond_reg,
        events.terms_p,
        events.cancel_p,
        events.no_show_p,
        events.refund_p,
        events.official_desc,
        events.supporting_desc,
        events.participating_desc,
        events.official_title,
        events.supporting_title,
        events.participating_title,
        events.order_number,
        venue.venue_en,
        venue.venue_ch,
        venue.added_by as venue_added_by,
        venue.added_date as venue_added_date,
        venue.updated_by as venue_updated_by,
        venue.updated_date as venue_updated_date,
        countries.country_id,
        countries.country_code,
        countries.country_name
FROM events 
INNER JOIN venue ON venue.venue_id = events.venue_id
INNER JOIN countries ON countries.country_id = events.country_id
WHERE events.display = '1' 
AND events.active = '1' 
ORDER BY IF(events.order_number = '', 1, 0),
        IF(events.order_number = '', NULL, events.order_number) ASC,
        IF(events.order_number = '', events.start_date, NULL) DESC

这将首先对来自您的第一个子查询的所有记录进行排序，然后是来自第二个子查询的所有记录。其中它将按 order_number ASCending 排序第一个，按 start_date DESCending 排序第二个。