将顺序放在正确的位置MySQL

Question

>我有这样的查询

SELECT COALESCE(p.name, 'total') AS `product name`, 
SUM(omd.quantity) AS `Qty(kg)`,
SUM(omd.quantity) / any_value(total_sum) * 100 AS `Qty(%)`, 
COUNT(om.id) AS `COunt Order`,
COUNT(om.id) / any_value(total_count) * 100 AS `Count Order(%)`
FROM order_match om
INNER JOIN order_match_detail omd
ON om.id = omd.order_match_id
INNER JOIN product p
on omd.product_id = p.id
INNER JOIN (select SUM(omd1.quantity) total_sum,
count(om1.id) total_count
FROM order_match om1
INNER JOIN 
order_match_detail omd1
ON om1.id = omd1.order_match_id
where om1.order_status_id in 
(4, 5, 6, 8)) totals
where om.order_status_id in (4, 5, 6, 8)
group by p.name with rollup;

运行该查询后，结果是这个(这只是假人(

+--------------+---------+--------+-------------+-----------------+
| Product Name | Qty(kg) | Qty(%) | COunt Order | Count Order (%) |
+--------------+---------+--------+-------------+-----------------+
| Product A    |      20 |     20 |          10 |              10 |
| Product B    |      30 |     30 |          10 |              10 |
| Product C    |      45 |     45 |          30 |              30 |
| Product D    |       5 |      5 |          50 |              50 |
| TOtal        |     100 |    100 |         100 |             100 |
+--------------+---------+--------+-------------+-----------------+

我想根据数量(公斤(下order by和订购

预期的结果是这样的

+--------------+---------+--------+-------------+-----------------+
| Product Name | Qty(kg) | Qty(%) | COunt Order | Count Order (%) |
+--------------+---------+--------+-------------+-----------------+
| Product C    |      45 |     45 |          30 |              30 |
| Product B    |      30 |     30 |          10 |              10 |
| Product A    |      20 |     20 |          10 |              10 |
| Product D    |       5 |      5 |          50 |              50 |
| TOtal        |     100 |    100 |         100 |             100 |
+--------------+---------+--------+-------------+-----------------+

所以这是我下订单后的查询

SELECT COALESCE(p.name, 'total') AS `product name`, 
SUM(omd.quantity) AS `Qty(kg)`,
SUM(omd.quantity) / any_value(total_sum) * 100 AS `Qty(%)`, 
COUNT(om.id) AS `COunt Order`,
COUNT(om.id) / any_value(total_count) * 100 AS `Count Order(%)`
FROM order_match om
INNER JOIN order_match_detail omd
ON om.id = omd.order_match_id
INNER JOIN product p
on omd.product_id = p.id
INNER JOIN (select SUM(omd1.quantity) total_sum,
count(om1.id) total_count
FROM order_match om1
INNER JOIN 
order_match_detail omd1
ON om1.id = omd1.order_match_id
where om1.order_status_id in 
(4, 5, 6, 8)) totals
where om.order_status_id in (4, 5, 6, 8)
group by p.name with rollup
order by omd.quantity DESC;

但是在我运行它之后，通知就像这样

Error COde :1221. Incorrect usage of CUBE/R0LLUP and ORDER BY

那么我应该编辑或添加哪个部分，以便可以按我想要的内容进行排序

Answer 1

不要在 GROUP BY UP 之后添加 ORDER BY。将查询转换为子查询(如果其版本为 8+，则转换为 CTE(，并在存储汇总结果的外部查询中对其输出进行排序：

SELECT *
FROM ( SELECT COALESCE(p.name, 'Total') AS `product name`, 
SUM(omd.quantity) AS `Qty(kg)`,
SUM(omd.quantity) / ANY_VALUE(total_sum) * 100 AS `Qty(%)`, 
COUNT(om.id) AS `Count Order`,
COUNT(om.id) / ANY_VALUE(total_count) * 100 AS `Count Order(%)`
FROM order_match AS om
INNER JOIN order_match_detail AS omd ON om.id = omd.order_match_id
INNER JOIN product AS p ON omd.product_id = p.id
CROSS JOIN ( SELECT SUM(omd1.quantity) AS total_sum,
COUNT(om1.id) AS total_count
FROM order_match AS om1
INNER JOIN order_match_detail AS omd1 ON om1.id = omd1.order_match_id
WHERE om1.order_status_id IN (4, 5, 6, 8)
) AS totals
WHERE om.order_status_id IN (4, 5, 6, 8)
GROUP BY p.name WITH ROLLUP 
) AS subquery
ORDER BY `product name`='Total', `Qty(kg)` DESC;