MySQL错误:#1247-不支持参考'业力'(对组功能的参考) [英] Mysql Error: #1247 - Reference 'karma' not supported (reference to group function)

问题描述

这是我下面的mysql查询.通过许多有用的问题和评论，我几乎快步入尾声了.该查询背后的思想是用户提交一个链接，应用程序插入两行，一个插入链接，另一行插入投票(默认投票，为什么用户不为自己提交的文件投票?)然后每一个投票只是另一行在投票表中，其karma_up或karma_down等于1(不久将更改为karma_delta以保存在额外的列上.我也有其中的流行度算法，这似乎在查询我的查询.)以下查询向我保证了该错误.

Here is my mysql query below. Through many helpful questions and comments I am almost at the end of my journey. The idea behind this query is a user submits a link, the application inserts two rows, one into links and another into votes (a default vote, why wouldn't a user vote for their own submission?) Then every vote is just another row in the votes table with a either a karma_up or karma_down equaling 1 (soon to be changed to karma_delta to save on the extra column. I also have the popularity algorithm in there which seems to be b0rking my query. Running the below query warrants me this error.

#1247 - Reference 'karma' not supported (reference to group function)

该查询的大部分要点是获取业力

The whole point of the majority of this query is to get the karma

SELECT links.*, (SUM(votes.karma_up) - SUM(votes.karma_down)) AS karma
FROM links, votes
WHERE links.id = votes.link_id
GROUP BY votes.link_id
ORDER BY (karma - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC
LIMIT 0, 100

在ORDER BY部分没有受欢迎度算法的情况下，查询运行完美，将votes表中的总业障总数相加，并在其值上附加一列.

Without the popularity algorithm at the ORDER BY part the query runs perfectly, adding the sum'ed up karma from the votes table and tacking on an extra column with it's value.

推荐答案

问题在这里:

`ORDER BY karma...

您不能通过被定义为别名的东西来订购.试试这个:

You can't order by something which is defined as an alias. Try this:

`ORDER BY ((SUM(votes.karma_up) - SUM(votes.karma_down)) - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC`

希望数据库可以弄清楚不要对其进行两次评估.如果不是，请先使用不带顺序的内部选择来创建别名，然后再使用另一个选择来进行排序.

Hopefully the DB can figure out not to evaluate it twice. If not, use an inner select without the order by first to create the alias, then use another select to order.

这篇关于MySQL错误:#1247-不支持参考'业力'(对组功能的参考)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！