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

发布时间：2020-12-13 13:07:21 所属栏目：PHP教程来源：网络整理

导读：我有以下查询来确定一个故事收到了多少票： 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 votesFROM st

我有以下查询来确定一个故事收到了多少票：

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

如果它适用于您的数据集,您可能需要检查此语句.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!