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

问题描述

我有一个名为"parts"的表格，组件存储有关电连接器的信息，包括触点，后壳等.作为组件一部分的所有零件在名为"assemblyID"的列中都有一个值.还有一个称为"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屋！