我有以下查询:
SELECT
l.id,
l.name,
l.url,
c.name,
COUNT(cc.id) AS popularity
FROM
links AS l,
categories AS c,
clicks AS cc
WHERE
l.category_id = c.id
AND
l.id = cc.link_id
AND
l.active = 1
ORDER BY
popularity DESC
LIMIT
10
正如你所看到的,我有3个表:链接、类别和点击。在"点击"中,每次点击数字时我都会保存一条记录。我想选择10个最受欢迎的链接,但我只得到所有点击加起来的最后一个记录。
我的查询出了什么问题?
提前感谢!
您似乎需要按分组
SELECT
l.id,
l.name,
l.url,
c.name,
COUNT(*) AS popularity
FROM
links AS l,
categories AS c,
clicks AS cc
WHERE l.category_id = c.id
AND l.id = cc.link_id
AND l.active = 1
GROUP BY l.id, c.name
ORDER BY popularity DESC LIMIT 10