首页 文章

选择Count（Distinct Value）返回1

提问于
浏览 次
8

我在SSMS 2005中设计了一个查询，看起来像这样：

SELECT COUNT(DISTINCT ColumnName) FROM Table WHERE ColumnName IS NOT NULL

当我使用COUNT（）运行查询时，它返回值1.当我在没有COUNT（）的情况下运行它时，SSMS报告正确的值，例如212条记录 .

有问题的列是数据类型numeric（16,0） .

对于那些可能会问的人，查询完整是：

SELECT COUNT(DISTINCT O_ID) FROM vEmployers
INNER JOIN vEnrolment ON O_ID = E_EnrolmentEmployer
WHERE E_START >= '01-AUG-2008' AND E_START < '01-AUG-2009'
AND O_ID IS NOT NULL AND O_ID IN (
    SELECT O_ID FROM vEmployers
    INNER JOIN vEnrolment ON O_ID = E_EnrolmentEmployer
    WHERE E_Start < '01-AUG-2008' and E_Start >= '01-AUG-2007'
)

此查询基本上给出了两个12个月期间的重复业务数字 .

So I'm wondering why "COUNT(DISTINCT ColumnName)" is returning 1 when "ColumnName IS NOT NULL" has been specified?

以下是运行SELECT TOP 10 DISTINCT ColumnName FROM ...等时的数据示例：

1346116
1346131
1346425
1346923
1349935
1350115
1350153
2594787
2821944
2879631
sql sql-server count distinct

5 回答

  • 5

    你可以运行这些查询：

    SELECT  COUNT(DISTINCT O_ID)
FROM    vEmployers
INNER JOIN
        vEnrolment
ON      O_ID = E_EnrolmentEmployer
WHERE   E_START >= '01-AUG-2008' AND
        E_START < '01-AUG-2009'
        AND O_ID IN
        (
        SELECT  O_ID
        FROM    vEmployers
        INNER JOIN
                vEnrolment
        ON      O_ID = E_EnrolmentEmployer
        WHERE   E_Start < '01-AUG-2008'
                AND E_Start >= '01-AUG-2007'
        )

    和

    SELECT  DISTINCT TOP 5 O_ID
FROM    vEmployers
INNER JOIN
        vEnrolment
ON      O_ID = E_EnrolmentEmployer
WHERE   E_START >= '01-AUG-2008' AND
        E_START < '01-AUG-2009'
        AND O_ID IN
        (
        SELECT  O_ID
        FROM    vEmployers
        INNER JOIN
                vEnrolment
        ON      O_ID = E_EnrolmentEmployer
        WHERE   E_Start < '01-AUG-2008'
                AND E_Start >= '01-AUG-2007'
        )
ORDER BY
        O_ID

    逐字逐句，没有改变什么？

    回复于
  • 0

    我'm guessing it'因为返回的所有行共享 O_ID 的相同值 . 您可以对每行唯一的键执行 COUNT(*) 或 COUNT() 以获取行计数 .

    回复于
  • 0
    SELECT   
  COUNT(*)  
FROM    vEmployers  
INNER JOIN vEnrolment ON O_ID = E_EnrolmentEmployer
WHERE 
        E_START >= '01-AUG-2008' 
        AND E_START < '01-AUG-2009'
        AND O_ID IS NOT NULL AND O_ID IN (
          SELECT O_ID FROM vEmployers
          INNER JOIN vEnrolment ON O_ID = E_EnrolmentEmployer
          WHERE E_Start < '01-AUG-2008' and E_Start >= '01-AUG-2007'
        )
GROUP BY
  O_Id
    回复于
  • 0

    使用数字（16,0）让我怀疑它是与数据类型相关的 . 在COUNT子句中添加CAST以将其强制转换为INT类型：

    Count(Distinct Cast(O_ID as Int))
    回复于
  • 0

    删除DISTINCT，您将获得所有行的计数 .

    回复于

相关问题