MySQL 5.1 到 5.7 更改 Order By 不再工作 [英] MySQL 5.1 to 5.7 change Order By no longer working

问题描述

我在 MySQL 根据 order by 定义的集合顺序创建计数器变量 SOrder 时遇到问题.它在 MySQL 5.1 中工作，但目前不在 5.7 中.5.1 中的原始查询的子查询 order by 是当时的标准可以接受的.在关闭 only_full_group_by 模式的 5.7 下，我将 order by 添加到外部，但即使子查询中的 order by 被注释掉(或根据 SQL 标准被忽略)，它也会输出 SOrder，好像它是随机的但不是基于列载体，PRIOR，ShipMethod，SingleModel.

SELECT载体，事先的，船法，集团订单，@row_no:=IF(@prev_val = ANY_VALUE(t.SingleModel),@row_no，@row_no + 1) 作为排序"，单一模型，COUNT(t.SingleModel) AS 'SingleModelTotQty',@prev_val:=ANY_VALUE(t.SingleModel)从(选择案件WHEN ANY_VALUE(W.hdr_user_defined_field18) LIKE '%Fedex%' THEN 'FEDEX'其他'UPS'结束为运营商"，案件什么时候ANY_VALUE(W.hdr_user_defined_field20) IN ('16', '26', '45', '88', '96')OR ANY_VALUE(W.hdr_user_defined_field20) IN ('19', '21', '22', '23', '24', '44', '76', '77', '78')然后'优先'其他'规范'以优先"结束，案件什么时候ANY_VALUE(W.hdr_user_defined_field20) IN ('88', '96', '80', '97')或 ANY_VALUE(W.hdr_user_defined_field20) IN ('76', '77', '78', '79')OR ANY_VALUE(W.`Ship-To Location`) != ''然后'S2S'其他'S2H'结束为 'ShipMethod',案件当 SUM(Quantity) = 1 THEN 'Single'什么时候总和(数量)>1 AND COUNT(`PO Number`) = 1然后'多个'WHEN COUNT(`PO Number`) >1 THEN '混合'结束为 'GroupOrder',案件当 SUM(Quantity) = 1 THEN `Supplier Item Nbr`当总和(数量)>1AND COUNT(`采购订单号`) = 1然后 ' 'WHEN COUNT(`PO Number`) >1 然后' '结束为单一模型"，`PO 编号` AS 'PONUM',案件当 SUM(Quantity) = 1 THEN ' 'ELSE SUM(数量)结束为 'QtyPerMulPO'从dropship.Walmart_FullPOs W在哪里处理日期 = CURDATE() - 5按采购订单编号"分组**ORDER BY Carrier , PRIOR DESC , ShipMethod , GroupOrder DESC , SingleModel DESC**) t,(SELECT @row_no:=0) x,(选择@prev_val:='') y在哪里GroupOrder = '单'GROUP BY Carrier , PRIOR , ShipMethod , SingleModelORDER BY t.Carrier , t.PRIOR DESC , t.ShipMethod , t.GroupOrder DESC , t.SingleModel DESC

结果

解决方案

您的 sql_mode 有问题.

从 MySQL 5.7.x 开始，默认的 sql 模式包括 ONLY_FULL_GROUP_BY.(5.7.5之前，MySQL不检测函数依赖，ONLY_FULL_GROUP_BY默认不开启)

ONLY_FULL_GROUP_BY:非确定性分组查询将被拒绝

有关更多详细信息，请查看

I have a problem with MySQL creating a counter variable SOrder based on a set order defined by order by. It was working in MySQL 5.1 but currently not in 5.7. Original query in 5.1 had the subquery order by and was acceptable to the standards back then. Under 5.7 with only_full_group_by mode off, I added the order by to the outside but even when the order by within the subquery was commented out (or ignored according to SQL standards), it output SOrder as if it's random but not based on Column Carrier, PRIOR, ShipMethod, SingleModel.

SELECT 
    Carrier,
    PRIOR,
    ShipMethod,
    GroupOrder,
    @row_no:=IF(@prev_val = ANY_VALUE(t.SingleModel),
        @row_no,
        @row_no + 1) AS 'SOrder',
    SingleModel,
    COUNT(t.SingleModel) AS 'SingleModelTotQty',
    @prev_val:=ANY_VALUE(t.SingleModel)
FROM
    (SELECT 
        CASE
            WHEN ANY_VALUE(W.hdr_user_defined_field18) LIKE '%Fedex%' THEN 'FEDEX'
            ELSE 'UPS'
        END AS 'Carrier',
        CASE
            WHEN
                ANY_VALUE(W.hdr_user_defined_field20) IN ('16' , '26', '45', '88', '96')
                        OR ANY_VALUE(W.hdr_user_defined_field20) IN ('19' , '21', '22', '23', '24', '44', '76', '77', '78')
            THEN 'PRIOR'
            ELSE 'NORM'
        END AS 'PRIOR',
        CASE
            WHEN
                ANY_VALUE(W.hdr_user_defined_field20) IN ('88' , '96', '80', '97')
                    OR ANY_VALUE(W.hdr_user_defined_field20) IN ('76' , '77', '78', '79')
                    OR ANY_VALUE(W.`Ship-To Location`) != ''
            THEN 'S2S'
            ELSE 'S2H'
        END AS 'ShipMethod',
        CASE
            WHEN SUM(Quantity) = 1 THEN 'Single'
            WHEN
                SUM(Quantity) > 1 AND COUNT(`PO Number`) = 1
            THEN 'Multiple'
            WHEN COUNT(`PO Number`) > 1 THEN 'Mixed'
        END AS 'GroupOrder',
        CASE
            WHEN SUM(Quantity) = 1 THEN `Supplier Item Nbr`
            WHEN SUM(Quantity) > 1
                    AND COUNT(`PO Number`) = 1
            THEN ' '
            WHEN COUNT(`PO Number`) > 1 THEN ' '
        END AS 'SingleModel',
        `PO Number` AS 'PONUM',
        CASE
            WHEN SUM(Quantity) = 1 THEN ' '
            ELSE SUM(Quantity)
        END AS 'QtyPerMulPO'
    FROM
        dropship.Walmart_FullPOs W
    WHERE
        ProcessedDate = CURDATE() - 5
    GROUP BY `PO Number`
    **ORDER BY Carrier , PRIOR DESC , ShipMethod , GroupOrder DESC , SingleModel DESC**
    ) t,
    (SELECT @row_no:=0) x,
    (SELECT @prev_val:='') y
WHERE
    GroupOrder = 'Single'
GROUP BY Carrier , PRIOR , ShipMethod , SingleModel
ORDER BY t.Carrier , t.PRIOR DESC , t.ShipMethod , t.GroupOrder DESC , t.SingleModel DESC

Result

解决方案

There is a problem with your sql_mode.

As of MySQL 5.7.x, the default sql mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default).

ONLY_FULL_GROUP_BY: Non-deterministic grouping queries will be rejected

For more details check the documentation of sql_mode

Method 1:

Check default value of sql_mode:

SELECT @@sql_mode

Remove ONLY_FULL_GROUP_BY from console by executing below query:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Method 2:

Access phpmyadmin for editing your sql_mode

• Login on phpmyadmin and open localhost
• Top on Variables present on the top in menu items and search out for sql mode
• Click on edit button to remove ONLY_FULL_GROUP_BY and save

这篇关于MySQL 5.1 到 5.7 更改 Order By 不再工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！