mysql order by 聚合函数工作不正确

Question

我在 order by 聚合函数方面遇到问题。问题是输出排序不正确。甚至可以是 order by 的聚合函数吗？他们每次都为我工作......

(problem is somewhere in order by MIN() function).

我尝试将所有变量转换为相同的数据类型，但效果不佳。

查询:

SELECT SQL_CALC_FOUND_ROWS
  COALESCE(MIN(IF(FIND_IN_SET("O", p.campaign), NULL, p.price) * (1 + v.vat / 100) * (1 - p.discount / 100)), p.price) as test,
  p.id as `id`

FROM
  products p
  LEFT JOIN products_variations pv ON p.id = pv.id_product
  LEFT JOIN photos_products_relation ppr ON p.id = ppr.id_product AND ppr.show_in_lead = 'Y'
  LEFT JOIN producers pr ON p.producer = pr.id
  INNER JOIN products_in_sections ps ON p.id = ps.id_product
  INNER JOIN vats v ON p.vat = v.id

WHERE ((pv.figure = 'Y') OR (pv.figure IS NULL)) AND (pr.active = 'Y' OR pr.id IS NULL) AND (
  ((COALESCE(pv.in_store, p.in_store) > 0) AND (ps.id_section IN (1868, 1855, 1758, 1746, 1875, 1947, 1870, 1952, 1895, 1942, 1737, 1741, 1744, 1828, 1887, 1762, 1773, 1893, 1781, 1897, 1929, 1920, 1896, 1908, 1751, 1775, 1782, 1770, 1778)) AND (p.figure = 'Y') AND
   (NOT FIND_IN_SET('O', coalesce(pv.campaign, '')))) AND (1)) AND (p.id_model IS NOT NULL)
GROUP BY p.id_model
ORDER BY COALESCE(MIN(IF(FIND_IN_SET("O", p.campaign), NULL, p.price) * (1 + v.vat / 100) * (1 - p.discount / 100)), p.price) ASC
LIMIT 36 OFFSET 0;

输出:

/image/qeK9f.png

如您所见，列测试排序不正确...

通常我会这样做

ORDER BY test

这可行，但我不想在输出中使用字段“test”。

(原始查询有更多选择字段)。

感谢您的回答:)

Answer 1

我不知道为什么它不起作用，我看不出出了什么问题。如果有必要的话，你总是可以做这样的事情。

SELECT ... (All columns but test)
FROM (  your query here as subquery) a
ORDER BY test ASC