如何在 Access 查询中选择前 1 个 - 并实际使其工作 [英] How to select top 1 in Access query - and actually get it to work

问题描述

首先让我指出，这是多个堆栈溢出问题的重复，所有问题都有答案——但没有一个能解决我的问题

First let me point out that this is a repeat of multiple stack-overflow questions that all have answers - yet none of them solve my problem

例如 - 这两个:第一条记录访问加入和如何在 Access 查询中选择前 10 名?

我的问题应该很简单 - 但是 - 显而易见的答案不起作用.

My problem should be simple - but - the obvious answers are not working.

我有两张桌子.客户和交易.

I have two tables. Client and Transactions.

我想返回具有最后销售日期的客户列表.容易对....为客户选择列，然后为销售日期执行一个子查询，将返回限制为 1 个项目.

I want to return a list of clients with the last sale date. Easy right.... Select the columns for the client and then for sale date do a subquery that limits the return to 1 item.

SELECT 
[Client].[LastName] as C1,
[Client].[FirstName] as C2,
(SELECT TOP 1 Transactions.SaleDate FROM Transactions WHERE Transactions.ClientID=Client.ClientID  ORDER BY Transactions.SaleDate Desc) as C3,
[Client].[ClientID] as C4
FROM [Client]

但是访问告诉我子查询有这个问题:"此子查询最多返回一条记录."

BUT access is telling me that the subquery has this problem: "At most one record can be returned by this subquery."

啊...但是堆栈溢出有一个答案此子查询最多可以返回一条记录."错误.

Ah... but stack overflow has an answer to the "At most one record can be returned by this subquery." error.

--->您的子查询返回多个结果."尝试使用 select top 1 修复数据"

--->"Your subquery is returning more than one result." "Try fixing the data using select top 1"

最多一条记录可以由这个子查询返回.(错误 3354)

嗯……太好了.

SQL 中是否还有其他内容可能会混淆我完全遗漏的内容?

Is there something else in the SQL that could be confusing it that I am totally missing?

推荐答案

将其添加为 JOIN 并尝试一下，Access sql 解析可能会被破坏(已经存在)，试试这个:

Add that as a JOIN and try it, Access sql parsing may be busted (been there), try this:

SELECT 
    c.[LastName] as C1,
    c.[FirstName] as C2,
    sd.maxsaledate as C3,
    c.[ClientID] as C4
FROM 
    [Client] c
    left join (
      select clientid, max(SaleDate) as maxsaledate from transactions group by clientid
    ) sd on
    c.ClientID = sd.ClientID

这篇关于如何在 Access 查询中选择前 1 个 - 并实际使其工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！