我有一个mysql查询,看起来像这样:
SELECT
SUM(reg_yr) AS reg_yr_total,
SUM(spot_as_reg_yr) AS spot_as_reg_yr_total
FROM foo
WHERE bar
GROUP BY baz
ORDER BY reg_yr_total
可以正常工作。但是,如果我想将ORDER BY子句更改为reg_yr_total+spot_as_reg_yr_total
,则会得到一个错误,说明Reference 'reg_yr_total' not supported (reference to group function)
。
为什么我可以单独使用这些列,但是当我试图将这两个列加在一起时,它就失败了?有办法解决这个问题吗?
如果不想选择其他列,请尝试以下操作:
SELECT
SUM(reg_yr) AS reg_yr_total,
SUM(spot_as_reg_yr) AS spot_as_reg_yr_total
FROM foo
WHERE bar
GROUP BY baz
ORDER BY SUM(reg_yr) + SUM(spot_as_reg_yr)
尝试将它们相加到另一个虚拟列:
SELECT
SUM(reg_yr) AS reg_yr_total,
SUM(spot_as_reg_yr) AS spot_as_reg_yr_total,
(reg_yr_total + spot_as_reg_yr_total) AS reg_yr_total
FROM foo
WHERE bar
GROUP BY baz
ORDER BY reg_yr_total
这是未经测试的,但应该可以工作。
如果这是一个错误的答案,请告诉我,我将很乐意删除它