我有下面的sql,它首先根据列"reported"选择最频繁的行
$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,并显示他们的报告
实际上,只需取条件的和并进行除法就很容易了。为了正确获得"报告",您需要使用内联视图来查找最高的报告。
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
演示
输出
| 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) / ....