两张表:1. stories,一栏列出超过10,000个故事标题,其他栏包括作者,日期,类别等。'id'是唯一标识符的列(每个故事自动递增)2. 评级。该表记录了每个故事的星级排名。这个表,有3列,一个自动递增的唯一id,表1的id(在表2中称为storyidr)/,排名值。
所以我想报告平均评分和总评分。
我已经使用了sql JOIN,我可以得到报告良好的平均评级。
SELECT s.*,
ROUND(AVG(r.rank),0)
AS avrank
FROM stories s
LEFT JOIN ratings
AS r
ON r.storyidr = s.id
GROUP BY s.id
ORDER BY RAND()
LIMIT 200;";
计数是另一回事。我正在尝试COUNT和UNION。什么都不起作用。是否有一种方法可以从已经查询的平均值中"提取"n的值?
知道平均值=(sum/n)
我不必这样做。如果我可以添加额外的SQL查询到当前的一个得到计数,这将是很好的。我只是不知道如何将计数函数添加到当前脚本?
与建议:$query="SELECT s.*, COUNT(r.rank) AS rkct ROUND(AVG(r.rank),0) AS avrank FROM stories s LEFT JOIN ratings AS r ON r.storyidr = s.id GROUP BY s.id ORDER BY RAND() LIMIT 5;";$result=mysqli_query($connection,$query);
<?php while ($data = mysqli_fetch_assoc($result)):$id = $data['id'];$author = $data['author'];$email = $data['email'];$title = $data['title'];$img_link = $data['img_link'];$smaller_img = $data['smaller_img'];$story_link = $data['story_link'];$page_path = $data['page_path'];$tag_alt = $data['tag_alt'];$category = $data['category'];$avgrate = $data['avrank'];$rankcount = $data['rkct'];
建议给我同样的错误:警告:mysqli_fetch_assoc()期望参数1是mysqli_result,布尔值在第88行中给出。
这是第88行:$avgratep = "Avg rating: "。avgrate美元。
"/5";似乎添加计数使avrank值为零或非数字?
就像调用avg
一样调用count
函数:
SELECT s.*,
ROUND(AVG(r.rank),0) AS avrank,
COUNT(*) AS countrank
FROM stories s
LEFT JOIN ratings
AS r
ON r.storyidr = s.id
GROUP BY s.id
ORDER BY RAND()
LIMIT 200;