php - 如何将数学函数应用于MySQL查询？

我有以下查询以确定一个故事获得了多少票:

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, s_title, 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

您可能需要检查此语句是否适用于您的数据集。