一次可以查询3件事吗? - Is it possible to query for 3 things at once?

Is it possible to query for 3 things at once?

本文关键字：3件查询一次 | 更新日期: 2023-09-27

我要给我的新页面添加一个5星投票系统。我有以下4个表:id, sid, rating, ipaddress。

我想做的(在我的查询中)是计算新闻条目的平均投票，计算总投票数+检查ip地址是否已经对该新闻条目投票。

这是我已经处理的投票平均/投票计数:

$result = mysql_query("SELECT AVG(rating) AS ave_rating, count(*) AS votes FROM news_ratings WHERE sid = $sid GROUP BY sid");
$obj = mysql_fetch_object($result);
$rating = $obj->ave_rating;
$votes = $obj->votes;

这可以修改，也检查"ipaddress"表，看看是否有一个匹配?

我想你的意思是你有4个列，而不是表…

您只需要在WHERE子句中添加逻辑AND:

SELECT 
  AVG(rating) AS ave_rating,
  count(*) AS votes 
FROM news_ratings
WHERE
  sid = $sid
  AND ipaddress = '$ipaddress'
GROUP BY sid

更新

在注释之后，为了用相同的查询返回一个IP匹配，您可以使用CASE语句在SELECT列表中创建一个名为ipmatch的列:

SELECT 
  AVG(rating) AS ave_rating,
  count(*) AS votes, 
  CASE WHEN ipaddress = '$ipaddress' THEN 'IP-MATCH' ELSE 'IP-NOMATCH' END AS ipmatch
FROM news_ratings
WHERE
  sid = $sid
GROUP BY sid