表结构如下:
create table test
(
count1 int null,
count2 int null,
name varchar(16) null,
type int null
);
sql1:一个聚合函数 使用别名 order by
select sum(count1) as total1
from test
group by type
order by total1 desc
可以查出结果,是正确的
sql2:两个聚合函数 使用别名相加 order by
select sum(count1) as total1,
sum(count2) as total2
from test
group by type
order by total1 + total2 desc;
报错:[42S22][1247] Reference 'total1' not supported (reference to group function)
百度了一下,资料比较少,不太懂。。
sql3:两个聚合函数 不使用别名相加 order by
select sum(count1) as total1,
sum(count2) as total2
from test
group by type
order by sum(count1) + sum(count2) desc;
可以查出正确值
现在我有点搞不懂了,order by 和聚合函数的别名有什么限制吗?
而且我发现好像只有涉及到 +
、-
等这些操作时,会有问题
1
uselessVisitor OP 啊。。。我好像懂了。。临时表里面没有 total1+total2 这个字段。。
|
2
zhangysh1995 2021-02-26 14:10:12 +08:00
可以用 MySQL CTE,https://dev.mysql.com/doc/refman/8.0/en/with.html
性能没研究过,如下: with mycte as ( select sum(count1) as total1, sum(count2) as total2 from test group by type ) select * from mycte order by (total1 + total2 ) desc; |