我如何在涉及多个表的多列上计算平均值和总数？

Question

这是我的不同表格：

computers (id,name)
monitors (id,name)
computer_monitor (id, computer_id,monitor_id)
useractivity (id,userid,timestamp,computer_monitor_id,ip)
useropinion (id,userid,computer_monitor_id,timestamp,rating)
user (id,name,email)

我希望能够搜索计算机或显示器的名称，并返回以下格式的一行内容：

computer name and/or monitor name
computer_monitor_id
avg(rate)
count(useractivity)

avg(rate)表示特定的computer_monitor_id的平均值，与名称匹配，count同理。

没有连接到显示器的计算机，在computer_monitor表中的显示器字段上的值为0，反之亦然。

useractivity和useropinion只包含来自computer_monitor表的ID。

- TimTimTim

---

@Siva，我在期望的输出中重新引入了OP的和/或措辞。（通常我理解“foo/bar”表示“foo（独占）或bar”。） - pilcrow

你是不是指 avg(rating)，而不是 avg(rate)？ - Andriy M

Answer 1

据我理解，查询应该围绕着computer_monitor表建立。所有其他表都与它连接，包括您想要获取统计信息的那些表。

SELECT
  c.name AS ComputerName,
  m.name AS MonitorName,
  uo.AverageRating,
  ua.ActivityCount
FROM computer_monitor cm
  LEFT JOIN computer c ON c.id = cm.computer
  LEFT JOIN monitor  m ON m.id = cm.monitor

  INNER JOIN (
    SELECT computer_monitor_id, AVG(rating) AS AverageRating
    FROM useropinion
    GROUP BY computer_monitor_id
  ) uo ON cm.id = uo.computer_monitor_id

  INNER JOIN (
    SELECT computer_monitor_id, COUNT(*) AS ActivityCount
    FROM useractivity
    GROUP BY computer_monitor_id
  ) ua ON cm.id = ua.computer_monitor_id

实际上，正如您所看到的，首先聚合useropinion和useractivity，然后再连接。这是为了避免当computer_monitor.id在useropinion和useractivity中都匹配多行时出现笛卡尔积效应。

- Andriy M

---

好的，非常不错，就快完成了。但是如果我想查看那些没有用户活动的呢？这样活动计数就变成零了。在我只有几行用户活动和超过1000台计算机的情况下，我希望能够搜索每个人。 - TimTimTim

@TimTimTim：如果我没有误解你的意思，你总是可以用LEFT JOIN代替INNER JOIN。因此，如果你想看到0而不是NULL，可以在选择列表中将ua.ActivityCount替换为COALESCE(ua.ActivityCount) AS ActivityCount。 - Andriy M

是的，谢谢！我现在唯一缺少的就是在c.name和m.name上进行搜索。其中一个可以工作，但两个都不行--> where c.name LIKE '%$name%' = 可以工作，但我想要从c.name和m.name中得到结果？有什么想法吗？ - TimTimTim

有什么办法可以将computer_monitor_id添加到一行中，然后从新表comment（id，timestamp，userid，computer_monitor）中为最新的时间戳添加一行。此外，在最后添加一个评分计数行。 - TimTimTim

@TimTimTim：1）关于添加computer_monitor_id - 我不确定我是否理解了你的意思，也许你只需要在选择列表中包含类似于uo.computer_monitor_id或ua.computer_monitor_id的内容。2）关于添加来自comment的最新行 - 在SO上搜索标记为[greatest-n-per-group + mysql]（http://stackoverflow.com/questions/tagged/greatest-n-per-group+mysql）的问题，那应该可以帮助你入门。如果你仍然有困难，请提出一个*新的*问题，询问如何将检索最后一条评论与任何查询组合起来。 - Andriy M

http://stackoverflow.com/questions/10394226/adding-new-rows-for-search-purposes-where-exsisting-inner-joins-are-implemented - TimTimTim

Answer 2

<?php
$res_comp = mysql_query("select * from computers where name = '$name'");
$res_monitor = mysql_query("select * from monitor where name = '$name'");
if(mysql_num_rows($res_comp) > 0)
{
$row_comp = mysql_fetch_array($res_comp);
$comp_id = $row_comp['id'];
$res_result = mysql_query("select computers.name, computer_monitor.id, count(computer_monitor_id) from computers, computer_monitor, useractivity where computers.id = '$comp_id' AND computer_monitor_id = '$comp_id' AND useractivity.computer_monitor_id = '$comp_id'");

}
// repeat the same for monitor also. then use mysql_fetch_array to show your data.
?>

希望这能有所帮助。

- Dev

---

|| bo_computers.id = '$comp_id' AND computer_monitor_id = '$comp_id' || 代表 bo.computer.id 和 computer_monitor_id 不是相同的 ID。computer_monitor_id 是连接 monitor_id 和 computer_id 之间的 ID。 - TimTimTim

Answer 3

这个可能有用...(一个表格显示计算机/监视器的关系，另一个表格是交叉引用表格，根据你的数据检查连接类型)

SELECT computers.name AS ComputerName
    , monitors.name AS MonitorName
    , AVG(useropinion.rating) AS AvgRating
    , COUNT(useractivity.id) AS ActivityCount
FROM computers
INNER JOIN computer_monitor ON (computers.id = computer_monitor.computer_id)
INNER JOIN useractivity ON (computers.id = useractivity.computer_id)
INNER JOIN monitors ON (computer_monitor.monitor_id = monitors.id)
INNER JOIN useropinion ON (computer_monitor.id = useropinion.computer_monitor_id) AND (monitors.id = useractivity.monitor_id)
INNER JOIN USER ON (useropinion.user_id = user.id) AND (useractivity.user_id = user.id)

- GDP

---

AND monitors.id = useractivity.monitor_id，在useractivity表中没有monitor_id这个字段，只有连接ID。我想知道是否应该改用（computer_monitor.id = useractivity.computer_monitor_id）？ - TimTimTim