查询子表中的最后一个元素 [英] Query on the last element in a subtable

问题描述

大家好，

我在一个表中有一个包含项目信息的数据库，在另一个表中有一系列日期。我想创建一个显示日期项目信息的报告，但前提是该日期是日期子表中的最后一个日期。在伪代码查询中，它将类似于


显示[项目数据]最后（[日期]。[发布日期]）=某个日期


两个表都有一个链接它们的项目ID字段。


谢谢，

Chris

Hi everyone,
I have a database with project information in one table, and a series of dates for each project in another. I want to create a report showing the project information for a date, but only if that date is the last date in the date subtable. In a pseudocode query, it would be something like

Display [Project Data] where last([Dates].[Release Date]) = "some date"

both tables have a project id field that link them.

Thanks,
Chris

推荐答案

在包含日期的表中，最后一个日期（您要查找的日期）总是最近的日期？也许再多解释一下为什么你会在这一条信息之后帮助我们。谢谢。


Pat

In the table that contains the dates, is the last date (the one you''re looking for) always the most recent date? Maybe a little more explanation about why you''re after that one piece of information will help us out. Thanks.

Pat

日期实际上是一个发布形式。 IE浏览器。 1A为1月的第一个版本，5B为5月的第二个版本。它们是文本形式。我正在尝试创建一个报告，该报告将在打开时提示发布日期，然后将显示所有这些项目作为最后日期。日期将按升序排列。


谢谢，

Chris

The dates are actually in a release form. Ie. 1A for the first release of january, and 5B for the second release in May. They are in text form. I am trying to create a report that will prompt for a release date when it''s opened, and then will show all those projects with that as the last date. The dates will be in ascending order.

Thanks,
Chris

当然可以编写一些简洁的SQL来实现这一目标。按照我设想的方式，将发布信息以三字符格式写出来会很有帮助，例如02A，05B，09C，11D等。问题是这个......


如果您采用某个特定项目的日期并从上到下（从最新到最旧）进行排序，那么10D或11E或12C等日期将会落到底部，因为从字符串处理的立场5B大于10D。

另一方面，如果你把5B写为05B，它将在排序上低于10D; 10D将正确地显示为最近的条目。


无论如何，我的想法是在SQL中使用称为TOP的东西。子句，允许用户从更大的记录集中仅选择TOP n 记录。理论上，如果记录按降序排序并且您为每个项目编号选择了TOP 1记录，那么您应该获得每个项目的最新日期。


这是否使任何意义？


Pat

It is certainly possible to write some succinct SQL to accomplish this. In the way that I envision doing it, it would be helpful to have the release information written out in a three-character format, such as 02A, 05B, 09C, 11D, etc. The issue is this...

If you take a particular project''s dates and order them top to bottom (newest to oldest) what will happen is that dates like 10D or 11E or 12C will fall to the bottom, because from a string processing standpoint 5B is greater than 10D.

On the other hand, if you write 5B as 05B, it will fall below 10D on the sort; 10D will properly show up as the more recent entry.

Anyway, my idea is to use something in SQL called the "TOP" clause, which allows the user to pick out only the TOP n records from a larger set of records. In theory, if the records are sorted in descending order and you pick out the TOP 1 record for each project number, you should get the most recent date for each project.

Does this make any sense?

Pat

这篇关于查询子表中的最后一个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！