ms 访问，需要获取具有不同列的所有行 [英] ms access, need to get all rows with a distinct column

问题描述

我有一张名为parts"的表格.它存储关于电连接器的信息，包括触点、后壳等.作为组件一部分的所有部件在称为组件ID"的列中都有一个值.还有一个名为partDefID"的列，在此列中，连接器的值为 2，联系人为 3.我需要获取所有具有唯一 assemblyID 的连接器的行.只需选择 partDefID 为 2 的行即可轻松获取表示连接器的行，但这将返回多行可能属于同一程序集的连接器.我只需要那些具有唯一 assemblyID 的连接器行.我怎样才能做到这一点?见下图:

I have a table called "parts" which stores information on electrical connectors including contacts, backshells etc. all parts that are part of an assembly have a value in a column called "assemblyID". There is also a column called "partDefID", in this column connectors will have a value of 2, contacts will be 3. I need to get rows for all connectors that have a unique assemblyID. It's easy to get rows that represent connectors just by selecting rows with a partDefID of 2 but this will return multiple rows of connectors that may be part of the same assembly. I need only those rows of connectors with a unique assemblyID. How can I do this? see image below:

我想要得到的只是下面显示的行之一，它们中的任何一个都可以，因为它们都是同一个程序集的一部分.只需要其中一行

what I am trying to get is just ONE of the rows shown below, any one of them would be fine as they are all part of the same assembly. just one of these rows needed

[更新]看来我的问题没有很好地形成，并且不赞成使用图像.不过，插入我的表格的文本版本看起来真的很可怕！我会努力做得更好.是的，我是 sql 和这个网站的新手

[update] it seems my question was not well formed and the use of images is frowned upon. Inserting a text version of my table looked REALLY horrible though! I'll try to do better. yes, I'm a newb at both sql AND this website

推荐答案

如果你只想要一个连接器"每个程序集 ID 的行，您可以使用子查询进行过滤.假设 PartRefID 是唯一键:

If you want just one "connector" row per assembly ID, you can filter with a subquery. Assuming that PartRefID is a unique key:

select *
from parts as p
where [PartRefID] = (
    select max(p1.[PartRefID])
    from parts as p1
    where p1.[AssemblyID] = p.[AssemblyID] and p1.[PartDefID] = 2
)

这篇关于ms 访问，需要获取具有不同列的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！