MYSQL:将 AVG 和 LIKE 语句合并为一个

Question

我正在为一个移动应用程序编写一个 PHP prepare 语句，该应用程序将搜索数据库中列出的啤酒，并允许用户将这些啤酒添加到他们自己的数据库中，还可以对它们进行评分和添加评论等。

我想要一个搜索功能，可以从 beers 表中选择所有啤酒，然后检查 myBeers 表以获取平均值(如果列出了该啤酒)。

在连接中，beerID 是两个表的公共(public)字段。

我的数据库中有两张表需要用到:一张是beer表，里面有5000条各种啤酒的记录，包括beerID、name、abv、abu、srm等，我需要能够像输入一样搜索此表。我有一个看起来像这样的查询:

SELECT *   
FROM beers   
WHERE name LIKE :name

另一个是 myBeers 表，它有 userID 和 beerID 以及评级和评论列。我有一个看起来像这样的查询:

SELECT AVG(rating) as ratingAverage 
FROM myBeers 
WHERE beerID = :beerID' 

我想将这些组合成一个语句，该语句将在 beers 表中搜索类似名称的名称，然后从 myBeers 表中提取平均值。如果没有平均值，那么我希望它显示为 0 而不是 null。

到目前为止，我已经尝试过加入查询和子查询——但是当我尝试使用 LIKE 选项时它们都卡住了。

我试过这个:

SELECT b.*, AVG(m.rating) AS avgRating
FROM beers b
INNER JOIN myBeers m
ON b.beerID = m.beerID
WHERE (SELECT name
        FROM beers 
        WHERE name LIKE 'Hocus')

然后我什么都没得到。

如果有人能帮助我，我将不胜感激。我试图包括一切。感谢您的帮助!

Answer 1

您应该能够使用如下内容:

SELECT b.name, 
  AVG(m.rating) AS avgRating 
FROM beers b 
LEFT JOIN myBeers m 
  ON b.beerID = m.beerID 
WHERE b.name LIKE '%Hocus%'
GROUP BY b.name

您的 WHERE 子句中不需要另一个查询。这将检查啤酒的 name 是否与您传入的字符串相同。您只需要决定在 LIKE 上使用通配符 % 的位置。我已将它添加到 Hocus 的开头和结尾，但您可能只需要在结尾处使用它。

此外，由于您使用的是聚合函数 Avg()，因此您需要使用 GROUP BY 子句。 GROUP BY 应包含选择列表中包含的其他列。您会注意到我删除了 select * 广告，将其替换为 select b.name。

此外，如果您想退回没有评级的啤酒，您将需要使用 LEFT JOIN 而不是 INNER JOIN。 LEFT JOIN 将返回 beers 表中的所有行，即使 myBeers 表中没有匹配的行也是如此。