我有一个查询，查询到一些产品有很多产品，列表将增加
SELECT pid, link, created_at FROM products ORDER BY timestamp DESC
但我还有另一个表，它包含pid和rating字段，pid是到products表的链接。
SELECT AVG(rating) FROM ratings GROUP BY pid
我如何查询，这样它就会提取出如下信息？
products.pid, products.link, products.created_at, AVG(ratings.rating)
在MySQL中，这应该会给出您想要的结果；
SELECT p.pid, p.link, p.created_at, AVG(r.rating) rating
FROM products p
LEFT JOIN ratings r
ON p.pid=r.pid
GROUP BY p.pid;
要测试的SQLfiddle。
顺便说一句，MySQL很特别，因为它可以让你进行不完整的GROUP BY查询，所以如果你想在另一个数据库上运行它（或者可能是出于风格原因），查询应该是；
SELECT p.pid, p.link, p.created_at, AVG(r.rating) rating
FROM products p
LEFT JOIN ratings r
ON p.pid=r.pid
GROUP BY p.pid, p.link, p.created_at;
在MS-SQL中，可以将其选择为：
SELECT p.pid,
p.link,
p.created_at,
(SELECT AVG(rating) FROM ratings r where r.pid = p.pid) as rating
FROM products p
ORDER BY timestamp DESC
SELECT p.pid, link, created_at, avg_rating
FROM products p
JOIN (SELECT pid, AVG(rating) avg_rating
FROM ratings
GROUP BY pid) a
ON p.pid = a.pid
ORDER BY timestamp DESC
当我使用上面的其他帖子时，它总是向我显示这个错误：
'Unknown column "timestamp" in "order clause"'
我相信时间戳已添加到products表中的created_at列中。所以，我在这个专栏中使用了排序。它将从产品表中选择最后一行，并用它打印AVG（评级）。
SELECT products.pid, products.link, products.created_at, AVG(ratings.rating)
FROM products, ratings
WHERE products.pid = ratings.pid
GROUP BY products.pid
ORDER BY products.created_at ASC
;
这个查询经过测试，对我来说运行良好。