php - 首先选择最高百分比的 php sql

Question

我有以下 sql，它首先根据“报告”列选择最重复出现的行

 $datan = mysql_query("
   SELECT *, COUNT(reported) AS ct
   FROM profile_reports
   WHERE open = '1'
   GROUP BY reported
   ORDER BY ct DESC
   LIMIT 1
 ") or die(mysql_error());

我希望我的 sql 还检查哪个“报告者”(每个报告者都是与用户关联的数字)具有最高百分比的有用报告，这是通过这种方式确定的:

((raction > 0 AND raction < 99 AND open = '0' AND reporter = 'reporter') / (reporter = 'reporter' AND open = '0')) * 100

...并首先显示百分比最高的行。这有点棘手，因为没有设置初始报告者。

这是一个示例表:

+----+----------+----------+-------+----------+
| id | reporter | reported | open  |  raction |
+----+----------+----------+-------+----------+
|  1 |       24 |    26    |  0    |  3       |
|  2 |       24 |    23    |  0    |  0       |
|  3 |       24 |    29    |  1    |          |
|  4 |       12 |    29    |  0    |  4       |
|  5 |       12 |    29    |  1    |          |
|  6 |       24 |    21    |  1    |  0       |
+----+----------+----------+-------+----------+

我想让它看到关于用户 29(列:reported)的更多报告，然后检查哪个报告用户(列:reporter)具有最佳百分比(基于上面的代码行)，在本例中是用户12、显示自己的报告

Answer 1

实际上很简单，只需将您的条件相加并相除即可。为了正确获得“已报告”，您需要使用内联 View 来查找最高报告。

SELECT pr.*, 
       ( Sum(pr.raction > 0 
             AND pr.raction < 99 
             AND pr.open = '0' 
             AND pr.reported = t.reported) / Sum(pr.reported = t.reported 
                                                 AND pr.open = '0') ) * 100 AS 
       usefull 
FROM   profile_reports pr, 
       (SELECT reported 
        FROM   profile_reports 
        WHERE  open = '1' 
        GROUP  BY reported 
        ORDER  BY Count(reported) DESC 
        LIMIT  1) t 
GROUP  BY reporter 
ORDER  BY usefull DESC 
LIMIT  1 

demo

输出

| ID | REPORTER | REPORTED | OPEN | RACTION | USEFULL |
-------------------------------------------------------
|  4 |       12 |       29 |    0 |       4 |     100 |

我还没有为你做所有的事情。如果除数为零，您将必须决定要做什么

注意除 MySQL 之外的几乎所有内容都需要使用 CASE

   SUM ( CASE WHEN raction > 0 AND .... THEN 1 ELSE 0 END) / ....