SQL Wizardry 第五部分 - 何时不要使用 DISTINCT

• 下载 SQL_Wizardry_05.zip - 825 B

 简介

我想退一步，谈谈一个常见的问题。我决定写的大部分东西都来自我在论坛上看到的问题。我经常看到人们做的一件事就是使用“DISTINCT”关键字来解决他们的问题。使用 DISTINCT 对于那些不知道的人，这里是它的工作原理。运行此 SQL 来创建一个非常简单的表

create table testDistinct
( 
  id int
)

insert into testdistinct values (1), (2), (1), (3), (4), (1), (3), (2), (5)

现在运行此

select * from testDistinct

然后运行这个

select distinct id from testdistinct

正如你所见，它删除了重复的值。如果我有很多列，所有列都需要相同，才能使该行被 DISTINCT 删除。因此，人们经常发现他们获得了太多的结果，并使用 DISTINCT 来减少它们。

一个更复杂的例子

现在运行此 SQL 来创建一些测试数据。这些数据假设我们正在为汽车修理商运行一个邮件服务器。我们有维修商、客户、客户的汽车以及每个维修商的邮件设置表。 这是 SQL

create table autoRepair
(
 id int identity,
 name varchar(50)
 )
 
 
 create table client
 (
   id int identity,
   repairerId int,
   name varchar(50) 
 )

 create table car
 (
   id int identity,
   clientId int,
   type varchar(50)  
 )
 
 create table repairMailSettings
 (
   repairId int,
   replyAddress varchar(100)
 )
 
 insert into autoRepair values('CO-OP Toyota'), ('Motors Holden')
 
 insert into client values (1, 'Fred Smith'), (1, 'Joe Kaputnuk'), (2, 'Bill Brown')
  
 insert into car values (1, 'Fred''s car'), (1, 'Fred''s other car'), (2, 'The Joe Mobile'),(3, 'Bill''s Brown Bomb')
 
 insert into repairMailSettings values (1, 'info@co-op.com'), (2, 'help@motors.com'), (1, 'info@co-op.com')

现在运行这个以获取所有车主、他们的汽车和维修商的列表

select ar.name as RepairerName, c.Name as ClientName, cr.Type as CarName from autoRepair ar 
 inner join client c on c.repairerId = ar.id
 inner join car cr on cr.clientId = c.id

这会返回：

RepairerName	ClientName	CarName
CO-OP Toyota	Fred Smith	Fred's car
CO-OP Toyota	Fred Smith	Fred's other car
CO-OP Toyota	Joe Kaputnuk	The Joe Mobile
Motors Holden	Bill Brown	Bill's Brown Bomb 

当然，如果你想向所有客户发送电子邮件，你也需要邮件设置，所以让我们包括这些

select ar.name as RepairerName, c.Name as ClientName, cr.Type as CarName, ms.replyAddress from autoRepair ar 
 inner join client c on c.repairerId = ar.id
 inner join car cr on cr.clientId = c.id
 inner join repairMailSettings ms on ms.repairId = ar.id

这将返回以下内容：

RepairerName	ClientName	CarName	replyAddress
CO-OP Toyota	Fred Smith	Fred's car	info@co-op.com
CO-OP Toyota	Fred Smith	Fred's car	info@co-op.com
CO-OP Toyota	Fred Smith	Fred's other car	info@co-op.com
CO-OP Toyota	Fred Smith	Fred's other car	info@co-op.com
CO-OP Toyota	Joe Kaputnuk	The Joe Mobile	info@co-op.com
CO-OP Toyota	Joe Kaputnuk	The Joe Mobile	info@co-op.com
Motors Holden	Bill Brown	Bill's Brown Bomb	help@motors.com

所有 CO-OP 记录都被复制了！！！ 所以，简单的解决方法是添加 DISTINCT

select distinct ar.name as RepairerName, c.Name as ClientName, cr.Type as CarName, ms.replyAddress from autoRepair ar 
 inner join client c on c.repairerId = ar.id
 inner join car cr on cr.clientId = c.id
 inner join repairMailSettings ms on ms.repairId = ar.id

然后我们得到

RepairerName	ClientName	CarName	replyAddress
CO-OP Toyota	Fred Smith	Fred's car	info@co-op.com
CO-OP Toyota	Fred Smith	Fred's other car	info@co-op.com
CO-OP Toyota	Joe Kaputnuk	The Joe Mobile	info@co-op.com
Motors Holden	Bill Brown	Bill's Brown Bomb	help@motors.com

看起来很棒，对吧？ 但我们是解决了问题，还是只是隐藏了它？

连接的工作原理

虽然表有时用于形成一对一的关系（就像我们的 repairMailSettings 表在理论上一样，但其想法是阻止一个表变得巨大并充满不同的信息），但大多数时候，它们定义的是一对多的关系。例如，弗雷德是一个人，但他拥有两辆车。 他的名字在客户表中出现一次，但当我们将客户连接到汽车时，因为他有两辆车，所以创建了两行，两行都具有相同的名字，但具有不同的汽车名称。

知道你在问什么

有时人们使用 DISTINCT 是因为他们未能提出正确的问题。 例如，有人前几天问为什么这两个查询会给出不同的结果

Select count(a.id) from a inner join b on a.id = b.id

并且

Select count(a.id) from a where a.id not in (select id from b)

原因是，b 有相同的 id 多次是完全有效的，但这会在连接中创建重复的行，并增加了计数。连接总是比子查询更好，但是，有时连接会问错误的问题。

这是一个 DISTINCT 完全有效的情况。 我们知道 a 中的 id 是唯一的，但在 b 中出现多次。但我们想要计算 a 中也出现在 b 中的项目。 这是一个如何做到这一点

Select count(distinct a.id) from a inner join b on a.id = b.id

理解结果，不要隐藏它

因此，DISTINCT 似乎解决了我们的汽车经销商问题，但它所做的只是修复了一个查询。仔细观察（对于那些错过它的人）会发现 repairMailSettings 中存在重复的行。这是问题所在。 我们需要向我们的表添加一个 UNIQUE 外键，而不是使用 DISTINCT，这样同一个 repairerId 就不会在我们的表中出现两次。如果我们选择了 DISTINCT 问题，并且用户更改了他们的设置，我们最终可能会在我们的数据库中拥有两组不同的设置。即使他们从未这样做过，每个使用此表的 SQL 都需要 DISTINCT 来继续隐藏问题。

结论

所以，这个故事的寓意是，如果你发现你得到重复的行，要理解为什么会这样，不要只是隐藏它。 DISTINCT 有时是完全有效的，但如果你使用它，你首先应该理解你为什么需要它，并确保没有其他解决方案。 有时，你所做的是隐藏一个问题，这个问题在稍后会重新出现。