在SQL Server中结合使用DISTINCT和GROUP BY [英] Using DISTINCT along with GROUP BY in SQL Server
问题描述
在SQL中使用DISTINCT和GROUP BY是否有目的?
下面是示例代码
选择不同的演员
从MovieDetails
GROUP BY演员
有人知道需要同时使用DISTINCT和GROUP BY以获得任何特定期望结果的情况吗?
(
使用 DISTINCT
删除 GROUP BY
子句中的组组
在通常使用 GROUPING SETS()
(或特殊分组集 ROLLUP()
或 CUBE()
),您可以使用 DISTINCT
再次删除分组集产生的重复值:
选择不同的演员
FROM (VALUES('a'),('a'),('b'),('b'))t(演员)
GROUP BY CUBE(演员,演员)
具有 DISTINCT
:
< pre class = lang-none prettyprint-override>
actors
------
NULL
a
b
没有 DISTINCT
:
< pre class = lang-none prettyprint-override>
actors
------
a
b
NULL
a
b
a
b
但是,除了提出学术要点之外,您为什么还要
使用 DISTINCT
查找唯一的聚合函数值
在一个不太牵强的示例中,您可能会对 DISTINCT
聚合值感兴趣,例如,多少不同个重复数字演员在那里?
SELECT DISTINCT COUNT(*)
FROM(VALUES(' a'),('a'),('b'),('b'))t(演员)
GROUP BY演员
答案:
计数
-----
2
使用 DISTINCT
删除具有多个 GROUP BY
列的重复项
当然是另一种情况:
SELECT DISTINCT actor,COUNT(*)
FROM(VALUES('a',1),('a',1),('b',1),('b',2))t(演员, id)
演员分组,id
具有 DISTINCT
:
演员数
------- ------
a 2
b 1
无 DISTINCT
:
演员数
- -----------
a 2
b 1
b 1
有关更多详细信息,我写了一些博客文章,例如关于组集
以及它们如何影响 GROUP BY
操作,或有关SQL操作的逻辑顺序(相对于操作的词汇顺序)。
Is there any purpose for using both DISTINCT and GROUP BY in SQL?
Below is a sample code
SELECT DISTINCT Actors
FROM MovieDetails
GROUP BY Actors
Does anyone know of any situations where both DISTINCT and GROUP BY need to be used, to get any specific desired results?
(The general usage of DISTINCT and GROUP BY separately is understood)
Use DISTINCT
to remove duplicate GROUPING SETS
from the GROUP BY
clause
In a completely silly example using GROUPING SETS()
in general (or the special grouping sets ROLLUP()
or CUBE()
in particular), you could use DISTINCT
in order to remove the duplicate values produced by the grouping sets again:
SELECT DISTINCT actors
FROM (VALUES('a'), ('a'), ('b'), ('b')) t(actors)
GROUP BY CUBE(actors, actors)
With DISTINCT
:
actors
------
NULL
a
b
Without DISTINCT
:
actors
------
a
b
NULL
a
b
a
b
But why, apart from making an academic point, would you do that?
Use DISTINCT
to find unique aggregate function values
In a less far-fetched example, you might be interested in the DISTINCT
aggregated values, such as, how many different duplicate numbers of actors are there?
SELECT DISTINCT COUNT(*)
FROM (VALUES('a'), ('a'), ('b'), ('b')) t(actors)
GROUP BY actors
Answer:
count
-----
2
Use DISTINCT
to remove duplicates with more than one GROUP BY
column
Another case, of course, is this one:
SELECT DISTINCT actors, COUNT(*)
FROM (VALUES('a', 1), ('a', 1), ('b', 1), ('b', 2)) t(actors, id)
GROUP BY actors, id
With DISTINCT
:
actors count
-------------
a 2
b 1
Without DISTINCT
:
actors count
-------------
a 2
b 1
b 1
For more details, I've written some blog posts, e.g. about GROUPING SETS
and how they influence the GROUP BY
operation, or about the logical order of SQL operations (as opposed to the lexical order of operations).
这篇关于在SQL Server中结合使用DISTINCT和GROUP BY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!