数据库的最新结果

发布于 2024-11-08 22:06:05 字数 378 浏览 0 评论 0原文

我需要仅显示最新项目的两个连接表的结果。我有以下疑问：

  SELECT project.id,
         project.created,
         COUNT(DISTINCT events.user_id) AS cnt
    FROM project
    JOIN events ON (events.project_id = project.id)
   WHERE project.creator = $creatorID
     AND events.user_id != $creatorID
ORDER BY project.created DESC
   LIMIT 1

出于某种原因，我继续获得第一个项目...我在这里缺少什么？

原文

I need display results from two joined tables only for the latest project. I have the following query:

  SELECT project.id,
         project.created,
         COUNT(DISTINCT events.user_id) AS cnt
    FROM project
    JOIN events ON (events.project_id = project.id)
   WHERE project.creator = $creatorID
     AND events.user_id != $creatorID
ORDER BY project.created DESC
   LIMIT 1

For some reason I keep on getting the first project... What am I missing here?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

睫毛溺水了 2024-11-15 22:06:05

您绝对走在正确的道路上。您似乎缺少聚合 COUNT() 的 GROUP BY 子句。试试这个：

SELECT
  project.id,
  project.created,
  count(DISTINCT events.user_id) AS cnt
FROM project
  JOIN events ON (events.project_id = project.id)
WHERE
  project.creator = $creatorID
  AND events.user_id != $creatorID
GROUP BY project.id, project.created
ORDER BY project.created DESC,
LIMIT 1

You're definitely on the right track. It seems you're missing a GROUP BY clause for your aggregate COUNT(). Try this:

SELECT
  project.id,
  project.created,
  count(DISTINCT events.user_id) AS cnt
FROM project
  JOIN events ON (events.project_id = project.id)
WHERE
  project.creator = $creatorID
  AND events.user_id != $creatorID
GROUP BY project.id, project.created
ORDER BY project.created DESC,
LIMIT 1

回复收藏 0 原文

~没有更多了~