这两个查询是否相同 - GROUP BY与DISTINCT？ [英] Are these two queries the same - GROUP BY vs. DISTINCT?

问题描述

这两个查询似乎返回相同的结果。这是巧合吗？还是他们真的是一样的？

<1>

  SELECT t.ItemNumber，
（SELECT TOP 1 ItemDescription 
 FROM Transactions 
 WHERE ItemNumber = t.ItemNumber 
 ORDER BY DateCreated DESC）AS ItemDescription 
 FROM Transactions t 
 GROUP BY t.ItemNumber 
  

<2>

  SELECT DISTINCT（t.ItemNumber），
（SELECT TOP 1 ItemDescription 
 FROM Transactions 
 WHERE ItemNumber = t.ItemNumber 
 ORDER BY DateCreated DESC）AS ItemDescription 
 FROM Transactions t 
  

有点解释：
我试图从一个充满事务的表中获得一个清晰的项目列表。对于每个项目，我正在查找ItemNumber（标识字段）和最近的ItemDescription。

解决方案

2让我挠了头一阵 - 我心想：你不能一列，这是什么意思？ - 直到我意识到发生了什么事。

当您有

  SELECT DISTINCT（t.ItemNumber）
  

不管 ，实际上要求 t.ItemNumber 的不同值！实际上，您的示例＃2的解析方式与

  SELECT DISTINCT 
（t.ItemNumber）
相同， 
（SELECT TOP 1 ItemDescription 
 FROM Transactions 
 WHERE ItemNumber = t.ItemNumber 
 ORDER BY DateCreated DESC）AS ItemDescription 
 FROM Transactions t 
  

带有句法正确但多余的括号，围绕 t.ItemNumber 。在整个结果集中，应用 DISTINCT 。

在这种情况下，由于您的 GROUP BY 按实际变化的列进行分组，您会得到相同的结果。实际上，对于SQL Server没有（在 GROUP BY 示例中）坚持在 GROUP BY 列表。

These two queries seem to return the same results. Is that coincidental or are they really the same?

1.

SELECT t.ItemNumber,
  (SELECT TOP 1 ItemDescription
   FROM Transactions
   WHERE ItemNumber = t.ItemNumber
   ORDER BY DateCreated DESC) AS ItemDescription
FROM Transactions t
GROUP BY t.ItemNumber

2.

SELECT DISTINCT(t.ItemNumber),
  (SELECT TOP 1 ItemDescription
   FROM Transactions
   WHERE ItemNumber = t.ItemNumber
   ORDER BY DateCreated DESC) AS ItemDescription
FROM Transactions t

A bit of explanation: I'm trying to get a distinct list of items from a table full of transactions. For each item, I'm looking for the ItemNumber (the identifying field) and the most recent ItemDescription.

解决方案

Your example #2 had me scratching me head for a while - I thought to myself: "You can't DISTINCT a single column, what would that mean?" - until I realised what is going on.

When you have

SELECT DISTINCT(t.ItemNumber)

you are not, despite appearances, actually asking for distinct values of t.ItemNumber! Your example #2 actually gets parsed the same as

SELECT DISTINCT
  (t.ItemNumber)
  ,
  (SELECT TOP 1 ItemDescription
   FROM Transactions
   WHERE ItemNumber = t.ItemNumber
   ORDER BY DateCreated DESC) AS ItemDescription
FROM Transactions t

with syntactically-correct but superfluous parentheses around t.ItemNumber. It is to the result-set as a whole that DISTINCT applies.

In this case, since your GROUP BY groups by the column that actually varies, you get the same results. I'm actually slightly surprised that SQL Server doesn't (in the GROUP BY example) insist that the subqueried column is mentioned in the GROUP BY list.

这篇关于这两个查询是否相同 - GROUP BY与DISTINCT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！