将 SQL 聚合函数与 JOIN 结合使用

发布于 2024-10-08 06:56:29 字数 732 浏览 7 评论 0原文

我有两个表 - tool_downloads 和 tool_configurations。我正在尝试检索数据库中每个工具的最新构建日期。 DB 的布局很简单。一个名为 tool_downloads 的表跟踪工具的下载时间。另一个表称为 tool_configurations，存储有关工具的实际数据。它们通过 tool_conf_id 连接在一起。

如果我运行以下省略日期的查询，我会返回 200 条记录。

SELECT DISTINCT a.tool_conf_id, b.tool_conf_id
FROM tool_downloads a
JOIN tool_configurations b 
ON a.tool_conf_id = b.tool_conf_id
ORDER BY a.tool_conf_id

当我尝试添加日期信息时，我得到了数十万条记录！这是严重失败的查询。

SELECT DISTINCT a.tool_conf_id, max(a.configured_date) as config_date, b.configuration_name
FROM tool_downloads a
JOIN tool_configurations b
ON a.tool_conf_id = b.tool_conf_id
ORDER BY a.tool_conf_id

我知道问题与分组/聚合数据和连接有关。我无法真正搜索谷歌，因为我不知道我遇到的问题的名称。任何帮助将不胜感激。

原文

I have two tables - tool_downloads and tool_configurations. I am trying to retrieve the most recent build date for each tool in my database. The layout of the DB is simple. One table called tool_downloads keeps track of when a tool is downloaded. Another table is called tool_configurations and stores the actual data about the tool. They are linked together by the tool_conf_id.

If I run the following query which omits dates, I get back 200 records.

SELECT DISTINCT a.tool_conf_id, b.tool_conf_id
FROM tool_downloads a
JOIN tool_configurations b 
ON a.tool_conf_id = b.tool_conf_id
ORDER BY a.tool_conf_id

When I try to add in date information I get back hundreds of thousands of records! Here is the query that fails horribly.

SELECT DISTINCT a.tool_conf_id, max(a.configured_date) as config_date, b.configuration_name
FROM tool_downloads a
JOIN tool_configurations b
ON a.tool_conf_id = b.tool_conf_id
ORDER BY a.tool_conf_id

I know the problem has something to do with group-bys/aggregate data and joins. I can't really search google since I don't know the name of the problem I'm encountering. Any help would be appreciated.

分享到QQ

分享到微博

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

发布评论

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

依靠 2024-10-15 06:56:29

解决方案是：

SELECT b.tool_conf_id, b.configuration_name, max(a.configured_date) as config_date
  FROM tool_downloads a
    JOIN tool_configurations b
      ON a.tool_conf_id = b.tool_conf_id
  GROUP BY b.tool_conf_id, b.configuration_name

Solution is:

SELECT b.tool_conf_id, b.configuration_name, max(a.configured_date) as config_date
  FROM tool_downloads a
    JOIN tool_configurations b
      ON a.tool_conf_id = b.tool_conf_id
  GROUP BY b.tool_conf_id, b.configuration_name

回复收藏 0 原文

~没有更多了~