姜戈。数据库查询：某一字段不同
我在数据库中有以下字段和数据：
FirstName LastName Date
John Davis 10-10-2011
Joe Gray 20-09-2011
Ann Davis 03-04-2010
Ann Bovis 01-04-2010
如何从具有最新日期和唯一姓氏的数据库 3 个项目中进行选择，所有其他字段都包含在结果中。这意味着，如果结果中有多个带有 LastName Davis 的项目，则应该只有一个具有最新日期的项目。 甚至不知道如何在纯 SQL 上做到这一点 - Django 看起来根本不可能。
编辑1。 SQL QUERY。
我在纯SQL上编写了查询：
SELECT TOP 3 *
FROM peopleTable as a1
WHERE (select count(*)FROM peopleTable as a2 where a2.LastName=a1.LastName and a2.Date > a1.Date)<=0
ORDER by Date desc
我想如果我在django中像纯SQL一样使用这个查询会更好。
I have following fields and data in DB:
FirstName LastName Date
John Davis 10-10-2011
Joe Gray 20-09-2011
Ann Davis 03-04-2010
Ann Bovis 01-04-2010
How can I select from DB 3 items with latest date and unique last name with all other fields included in result. That is mean if there are more than one item with LastName Davis in result should be only one with latest date.
Don't even know how to do it on pure SQL - and Django looks impossible at all.
EDIT1. SQL QUERY.
I wrote query on pure SQL:
SELECT TOP 3 *
FROM peopleTable as a1
WHERE (select count(*)FROM peopleTable as a2 where a2.LastName=a1.LastName and a2.Date > a1.Date)<=0
ORDER by Date desc
I guess it would be better if I use this query in django just as pure SQL.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你可以这样做：
或
，但不能两者兼而有之。如果您想获取姓氏 Davis 的最新日期，还应检索哪些其他字段，firstname=John 还是firstname=Ann？
我猜测您希望结果如下所示：
Ann Davis 行消失了，因为她不是表中最新的 Davis。正确的？
在这种情况下，你的原始 SQL 看起来像这样：
你可以使用 Item.objects.raw() 选项。您还可以使用
extra(where=…)You can do this:
or
but you can't do both. If you want to get the most recent date for last name Davis, which other fields should be retrieved, firstname=John or firstname=Ann?
I am guessing that you want the result to look like this:
Where the Ann Davis row is gone because she isn't the most recent Davis in the table. Correct?
In which case your raw SQL looks something like:
Which you can write up in Django using the Item.objects.raw() option. You can also use
extra(where=…)请参阅聚合上的文档
猜你想要类似
必须聚合的 东西对聚合查询中包含的所有字段进行覆盖或分组。在您的情况下，您需要 max(data) 和 group_by 姓氏。你还必须为名字选择一些聚合，比如 max
注意，似乎有一个 postgres bug 关于在炭火场上聚集
see the docs on aggregation
guess you want something like
you have to aggregate over or group by all fields included in an aggregate query. in your case you want max(data) and group_by lastname. you have to choose some aggregation for firstname as well, say max
note that there seems to be a postgres bug on aggregating over charfields
你实际上不能通过查询来做到这一点；对于 SQL 来说，它的逻辑太多了。然而，基于字典键是唯一的这一事实，你只需发挥一点聪明才智就可以得到你想要的东西。
首先，获取按日期升序排序的查询集。这可能看起来违反直觉，因为您想要最新的日期，但在接下来的步骤中它将有意义：
接下来，我们将在列表理解中使用它来创建
(key, value)元组（在 Python 3+ 中，你实际上可以进行字典推导，但由于我认识的人实际上使用 Python 3+ 的人并不多，所以我以这种方式详细说明）：最后，我们将这个元组列表转换为字典
：现在将有一个字典，其中每个姓氏键都是唯一的。由于字典键必须是唯一的，因此每个重复姓氏的最后一个值是实际出现的值。由于列表按日期升序排序，因此最后一个值是“最新”的。
您可以将此字典转换回直接列表，或者直接对其进行迭代。唯一的问题是您不再处理查询集，因此您无法执行任何进一步的过滤器等。请记住这一点，并在完全构建查询后最后执行这些步骤。
当然，如果您愿意的话，您可以将所有这些都作为一个班轮来完成：
You can't really do it with a query; it's just too much logic for SQL. However, based on the fact that dictionary keys are unique, you can get what you want with a little ingenuity.
First, get the queryset ordered by date ascending. This may seem counter-intuitive because you want the latest date, but it'll make sense in the next steps:
Next, we'll use this in a list comprehension to create
(key, value)tuples (in Python 3+, you can actually do dictionary comprehensions, but since not many people I know are actually using Python 3+, I'm detailing it this way):Finally, we convert this list of tuples into a dictionary:
You'll now have a dictionary where each last_name key is unique. Since dictionary keys must be unique, the last value of each duplicated last name was the one that actually made it in. Since the list was ordered by date ascending, the last value was the "lastest".
You can convert this dictionary back into a straight list or just iterate over it as it is. The only catch is that you're no longer dealing with a QuerySet, so you can't do any further filters, etc. Just keep that in mind and do these steps last after you've fully built your query.
And, of course, you can do that all as a one liner if you're so inclined: