SQL的DISTINCT子句如何工作? [英] How SQL's DISTINCT clause works?

问题描述

我正在寻找有关SQL上DISTINCT子句如何工作的答案(如果有区别，SQL Server 2008会有所作为)，该查询具有多个连接的表?

I'm looking for the answer on how DISTINCT clause works in SQL (SQL Server 2008 if that makes a difference) on a query with multiple tables joined?

我的意思是SQL引擎如何使用DISTINCT子句处理查询?

I mean how the SQL engine handles the query with DISTINCT clause?

我问的原因是，我经验丰富的同事告诉我SQL将DISTINCT应用于每个表的每个字段.对我来说似乎不太可能，但是我想确保...

The reason I'm asking is that I was told by my far more experienced colleague that SQL applies DISTINCT to every field of every table. It seems unlikely for me, but I want to make sure....

例如，有两个表:

CREATE TABLE users
(
u_id INT PRIMARY KEY,
u_name VARCHAR(30),
u_password VARCHAR(30)
)

CREATE TABLE roles
(
r_id INT PRIMARY KEY,
r_name VARCHAR(30)
)

CREATE TABLE users_l_roles
(
u_id INT FOREIGN KEY REFERENCES users(u_id) ,
r_id INT FOREIGN KEY REFERENCES roles(r_id) 
)

然后进行以下查询:

SELECT          u_name
FROM            users 
INNER JOIN      users_l_roles ON users.u_id = users_l_roles.u_id
INNER JOIN      roles ON users_l_roles.r_id = roles.r_id 

假设有两个角色的用户，则上面的查询将返回两个具有相同用户名的记录.

Assuming there was user with two roles then the above query will return two records with the same user name.

但是此查询具有不同之处:

But this query with distinct:

SELECT DISTINCT u_name
FROM            users 
INNER JOIN      users_l_roles ON users.u_id = users_l_roles.u_id
INNER JOIN      roles ON users_l_roles.r_id = roles.r_id 

将仅返回一个用户名.

问题是SQL是比较所有联接表(u_id，u_name，u_password，r_id，r_name)中的所有字段，还是只比较查询中的命名字段(u_name)并区分结果?

The question is whether SQL will compare all the fields from all the joined tables (u_id, u_name, u_password, r_id, r_name) or it will compare only named fields in the query (u_name) and distinct the results?

推荐答案

DISTINCT过滤出返回的字段的重复值.

DISTINCT filters out duplicate values of your returned fields.

一种真正简化的查看方式是:

A really simplified way to look at it is:

• 它根据您的FROM和WHERE子句构建您的整体结果集(包括重复项)
• 它根据您要返回的字段对该结果集进行排序
• 它会删除这些字段中的所有重复值

• It builds your overall result set (including duplicates) based on your FROM and WHERE clauses
• It sorts that result set based on the fields you want to return
• It removes any duplicate values in those fields

从语义上讲，它等同于GROUP BY，其中所有返回的字段都在GROUP BY子句中.

It's semantically equivalent to a GROUP BY where all returned fields are in the GROUP BY clause.

这篇关于SQL的DISTINCT子句如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！