mysql - 在一个查询中同时使用 GROUP BY 和 ORDER BY

Question

有人告诉我不能在一个 MySQL 查询中使用 GROUP BY 和 ORDER BY。这是查询的缩写版本 -

SELECT n.colorName, n.colorComp, n.colorID, SUM(n.gallons) AS TotalGallons
FROM netTran n, Store m, Product p
WHERE ((n.store = m.store) and m.state = "FL")
AND ((n.salesNbr = p.salesNbr) AND (p.intExt = "EXTERIOR" OR p.intExt = "INT/EXT"))
AND ((n.clrnt1 = "L1") AND (n.clrnt1 = "R3"))
GROUP BY n.colorComp, n.colorID
ORDER BY TotalGallons DESC;

有人告诉我，将 ORDER BY 与 GROUP BY 结合使用会得到不同的结果，并且 ORDER BY 工作的唯一方法是将主查询嵌套在

SELECT * FROM
(query)
ORDER BY TotalGallons DESC;

对吗？

Answer 1

使用查询作为

SELECT n.colorName, n.colorComp, n.colorID, SUM(n.gallons) AS     TotalGallons
FROM netTran n, Store m, Product p
WHERE ((n.store = m.store) and m.state = "FL")
AND ((n.salesNbr = p.salesNbr) AND (p.intExt = "EXTERIOR" OR p.intExt = "INT/EXT"))
AND ((n.clrnt1 = "L1") AND (n.clrnt1 = "R3"))
GROUP BY n.colorName, n.colorComp,n.colorID
ORDER BY TotalGallons DESC;

您可以在单个查询中使用分组依据和排序依据。但是如果要聚合列，则需要提供所有列