mysql-order-by-after组？

gfttwv5a 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(246)

嗨，我有sql结果

我的问题是

SELECT sales_flat_order.entity_id AS entity_id,
      CASE
          WHEN sales_flat_order.`grand_total` BETWEEN '0' AND '100.99' THEN '0->100.99'
          WHEN sales_flat_order.`grand_total` BETWEEN '101' AND '150.99' THEN >'101-150.99'
          WHEN sales_flat_order.`grand_total` BETWEEN '151' AND '200.99' THEN >'151-200.99'
          WHEN sales_flat_order.`grand_total` BETWEEN '201' AND '250.99' THEN >'201-250.99'
          WHEN sales_flat_order.`grand_total` BETWEEN '251' AND '300.99' THEN >'251-300.99'
          WHEN sales_flat_order.`grand_total` BETWEEN '301' AND '350.99' THEN >'301-350.99'
          WHEN sales_flat_order.`grand_total` BETWEEN '351' AND '400.99' THEN >'351-400.99'
          WHEN sales_flat_order.`grand_total` BETWEEN '401' AND '450.99' THEN >'401-450.99'
          WHEN sales_flat_order.`grand_total` BETWEEN '451' AND '500.99' THEN >'451-500.99'
          ELSE '501+'
      END AS 'order_range',
      CONCAT(MONTHNAME(sales_flat_order.created_at), ' - ', >YEAR(sales_flat_order.created_at)) AS 'Month',
      CONCAT('$', FORMAT(SUM(sales_flat_order.`grand_total`), 2)) AS 'Sales',
      sales_flat_order.`grand_total` AS Amount,
      COUNT(sales_flat_order.entity_id) AS 'Orders' 
FROM sales_flat_order GROUP BY MONTH,order_range 
ORDER BY Amount ASC

我需要通过升序第二列'顺序范围'的结果。
我期望的答案应该是

Feb 2015 | 0-100  | XXX <br>
Feb 2015 | 101-150| XXX <br>
Feb 2015 | 150-200| XXX <br>
Feb 2015 | 200-250| XXX <br>
Feb 2015 | 250-300| XXX <br>

sql mysql group-by sql-order-by

来源：https://stackoverflow.com/questions/49919617/mysql-order-by-after-group

2条答案

按热度按时间

ohfgkhjo1#

只需使用以下内容更新“order by”查询部分：

ORDER BY MONTH, FIELD(order_range,
'0->100.99',
'101-150.99',
'201-250.99',
...
)

赞(0）回复(0）举报 2021-06-26

0kjbasz62#

你可以用 SUBSTRING_INDEX 在这里：

ORDER BY
    Month,
    CAST(SUBSTRING_INDEX(order_range, '-', 1) AS UNSIGNED);

下面是一个演示，演示了这种排序逻辑的工作原理：

演示

编辑：
不要以文本形式报告年和月，月在前，年在后，因为你也会在那里引起排序问题。相反，使用 DATE_FORMAT :

ORDER BY
    DATE_FORMAT(sales_flat_order.created_at, '%Y-%m'),
    CAST(SUBSTRING_INDEX(order_range, '-', 1) AS UNSIGNED);

赞(0）回复(0）举报 2021-06-25

我来回答

mysql-order-by-after组？

2条答案

演示

相关问题

热门标签

最新问答