如果有 count 子句，SQL Distinct 将无法工作 [英] SQL Distinct cannot working if having count clause

问题描述

我的 sql 查询有问题(选择不同的不起作用).

I am having trouble with my sql query (Select distinct didnt work).

我的sql是:

select distinct
count(T2.Column1)
from Table1 t2
where T2.Column1='2017-05-210'

实际第1列数据只有3条数据，但是输出是12

The actual Column 1 data only have 3 data, But the output is 12

铌:- Column1 数据与 Column2 存在一对多的情况，

Nb : - Column1 data is having 1 to Many situation with Column2,

实际数据如下:

Column 1    Column 2
   1          A
   1          B
   1          C
   1          D
   2          A
   2          B
   2          C
   2          D
   3          A
   3          B
   3          C
   3          D

谁能帮帮我?非常感谢您的关注.

Can anyone help me? Really appreciate for your attention.

谢谢！

推荐答案

您的示例数据、结果和查询不匹配.

Your sample data, result and query do not match.

但是，您的查询所做的是:

However, what your query does is:

1. 查找具有 column1 = '2017-05-210' 的所有记录.
2. 计算column1 不为空的所有这些记录(对于所有这些记录都是如此，因为column1 = '2017-05-210')..立>
3. 这会产生一个数字(一行，一列).但是您还使用 DISTINCT 表示要从结果行中删除任何重复项.只有一行不能重复，所以这里的函数是多余的.

1. Find all records with a column1 = '2017-05-210'.
2. Count all of these records where column1 is not null (which is true for all these records, as column1 = '2017-05-210').
3. This results in one number (one row, one column). But you additionally say with DISTINCT that you want to remove any duplicates from your result rows. With one row only there can be no duplicates, so the function is superfluous here.

所以想想你真正想要数的东西.您使用 COUNT(DISTINCT column) 计算不同的值(即忽略重复项)，但是 COUNT(DISTINCT column1) 当然会返回 1，因为您只是在寻找 一个值，它是 '2017-05-210' (如果没有与此值匹配的记录，则为零).

So think about what you want to count really. You count distinct values (i.e. count ignoring duplicates) with COUNT(DISTINCT column), but COUNT(DISTINCT column1) would return 1 of course, because you are only looking for one value which is '2017-05-210' (or zero in case there is no record matching this value).

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