SQL服务器 - 在一列DISTINCT [英] SQL Server - DISTINCT on one column

问题描述

我有一个查询在那里我参加3 ....关系A，B和;.联接是一个唯一的ID。表B包含彼此相似行和我想做一个DISTINCT的表B中的FK列（PK从A）。

I have a query where I join 3 relations....A, B &. The join is on a unique id. Table B contains rows similar to each other and I want to do a DISTINCT on a FK column in table B (the PK from A).

为了更清楚：

    A              B           C
---------      ---------   ---------
No. (PK)       Id(PK)       Id (PK)
Name           Role         Address
               No.(FK)      No.(FK)

表B可以从表A的人多实例我想这是直接从A，B和字段的查询; C对号场加盟。表B可以有几行与号列的值相同，所以我想在第列执行DISTINCT。

Table B can have multiple instances of people from table A. I want a query which pulls fields from A, B & C joined on the No. field. Table B can have several rows with the same value for the No. column, therefore I want to perform a DISTINCT on the No. column.

我怎样才能做到这一点？

How can I do this?

示例数据：

NAME          ROLE       ADDRESS
---------------------------------------
John Smith    Manager    1, The Village
Dawn French   Secretary  2, The City
John Smith    SQL Dev    1, The Village
Terry Tibbs   HR Manager 8, The Road

这是一个例子关系上的A，B和加盟; C：

This is an example of a join on relations A, B & C:

 SELECT A.Name, B.Role, C.Address 
 FROM A, B, C 
 WHERE A.No = B.No AND B.No = C.No

表B可能包含几个约翰·史密斯（同一人），谁拥有多个角色 - >所以我想做一个DISTINCT上唯一标识一个人的号。这是表A的日EPK（无）。

Table B could contain several John Smiths (the same person) who has multiple roles -> therefore I want to do a DISTINCT on the No. that uniquely identifies a person. This is th ePK of Table A (No.).

推荐答案

在你的解决方案一个人可以有多个角色和多个地址。 一列独特的子查询需要招（其他民间已经表现出你）。但是，如果您的查询显示主（或只是其中之一）的作用，主要地址为每一个人，你的查询可能会简单得多。

In you solutions one person may have multiple roles and multiple addresses. "One column DISTINCT" requires subquery trick (as other folk already showed you). But if your query is to show primary (or just one of many) role and primary address for every one person, your query may be much simpler.

SELECT
A.Name,
(SELECT TOP 1 Role FROM B WHERE B.No = A.No) AS PrimaryRole, -- you may add ORDER BY here
(SELECT TOP 1 Address FROM C WHERE C.No = A.No) AS PrimaryAddress, -- add ORDER BY
FROM A
ORDER BY A.Name

这篇关于SQL服务器 - 在一列DISTINCT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！