php – MySql逻辑顺序依据

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

导读：我目前有这样的查询： SELECT 'id','clanid','name','level','exp','warwinpercent','warswon','warslost','warstied','playercount','score'FROM clansWHERE warswon = 100ORDER BY warwinpercent DESC,warswon DESC; 现在它有效,但最终它不像我希望的那样

我目前有这样的查询：

SELECT 'id','clanid','name','level','exp','warwinpercent','warswon','warslost','warstied','playercount','score'
FROM clans
WHERE warswon >= 100
ORDER BY warwinpercent DESC,warswon DESC;

现在它有效,但最终它不像我希望的那样合乎逻辑……

例如.

假设有一排战争胜率为99.5738％,赢得了208战.
而另一个拥有100％战争赢率和103场战争赢得了胜利.

我希望99％的行高于100％的行.我有什么办法让这个工作吗？

我所拥有的等式是：

warinpercent = warswon/(warswon+warstied+warlost)*100
order by warwinpercent
if warwinpercent are in a range of 3% then order by warswon between them.

解决方法

我建议像 How Not To Sort By Average Rating一样使用订购

PROBLEM:

You need some sort of “score” to sort by.

WRONG SOLUTION #1: Score = (Positive ratings) – (Negative ratings)

WRONG SOLUTION #2: Score = Average rating = (Positive ratings) /
(Total ratings)

CORRECT SOLUTION: Score = Lower bound of Wilson score confidence
interval for a Bernoulli parameter

enter image description here

演示：

CREATE TABLE clans(id INT,name VARCHAR(100),warswon INT,warslost INT);

INSERT INTO clans VALUES (1,'aaa',208,6),(2,'bbb',103,0);

SELECT id,name,warswon,warslost,((warswon + 1.9208) / (warswon + warslost) - 
                 1.96 * SQRT((warswon * warslost) / (warswon + warslost) + 0.9604) / 
                          (warswon + warslost)) / (1 + 3.8416 / (warswon + warslost)) 
       AS ci_lower_bound 
FROM clans 
ORDER BY ci_lower_bound DESC;

SqlFiddleDemo

输出：

╔═════╦═══════╦══════════╦═══════════╦════════════════════╗
║ id  ║ name  ║ warswon  ║ warslost  ║   ci_lower_bound   ║
╠═════╬═══════╬══════════╬═══════════╬════════════════════╣
║  2  ║ bbb   ║     103  ║        0  ║ 0.9640439675800224 ║
║  1  ║ aaa   ║     208  ║        6  ║ 0.9401908847803808 ║
╚═════╩═══════╩══════════╩═══════════╩════════════════════╝

（编辑：李大同）

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