我有以下查询来确定一个故事收到了多少票：
SELECT s_id,s_title,s_time,(s_time-now()) AS s_timediff,( (SELECT COUNT(*) FROM s_ups WHERE stories.q_id=s_ups.s_id) - (SELECT COUNT(*) FROM s_downs WHERE stories.s_id=s_downs.s_id) ) AS votes FROM stories
我想对即将到来的故事应用以下数学函数(我认为这是reddit使用的) –
http://redflavor.com/reddit.cf.algorithm.png
我可以在应用程序端执行该功能(我现在正在做),但我不能按功能提供的排名对其进行排序.
有什么建议吗？
试试这个：
SELECT s_id,log10(Z) + (Y * s_timediff)/45000 AS redditfunction
FROM (
SELECT stories.s_id,stories.s_title,stories.s_time,stories.s_time - now() AS s_timediff,count(s_ups.s_id) - count(s_downs.s_id) as X,if(X>0,1,if(x<0,-1,0)) as Y,if(abs(x)>=1,abs(x),1) as Z
FROM stories
LEFT JOIN s_ups ON stories.q_id=s_ups.s_id
LEFT JOIN s_downs ON stories.s_id=s_downs.s_id
GROUP BY stories.s_id
) as derived_table1
如果它适用于您的数据集,您可能需要检查此语句.
