代码之家 › 专栏 › 技术社区 › GateKiller

选择计数(不同值)返回1

distinct count sql-server sql

GateKiller · 技术社区 · 15 年前

我正在设计一个查询 SSMS 2005年,看起来像这样:

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

当我使用COUNT()运行查询时,它返回值1。当我不使用COUNT()运行查询时,SSMS报告正确的值。

所讨论的列是数据类型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个月期间的重复业务数据。

以下是从中选择前10个不同列名时的数据示例。。。etc正在运行:

5 回复 | 直到 15 年前

Turnkey 15 年前

使用数字(16,0)使我怀疑它与数据类型有关。在COUNT子句中添加强制转换以将其强制转换为INT类型:

Count(Distinct Cast(O_ID as Int))

Håvard S 15 年前

我猜这是因为返回的所有行对于 O_ID COUNT(*) 或 COUNT() 在每行唯一的键上获取行数。

Turnkey 15 年前

删除DISTINCT,您将获得所有行的计数。

GateKiller 15 年前

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

Benoît Vidis 15 年前

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