sql-server - 使用计数的意外输出sql server

Question

我使用的是 sql-server 2012

查询是:

CREATE TABLE TEST ( NAME VARCHAR(20) );
INSERT TEST
        ( NAME
        )
        SELECT NULL
        UNION ALL
        SELECT 'James'
        UNION ALL
        SELECT 'JAMES'
        UNION ALL
        SELECT 'Eric';

SELECT NAME
      , COUNT(NAME) AS T1
      , COUNT(COALESCE(NULL, '')) T2
      , COUNT(ISNULL(NAME, NULL)) T3
      , COUNT(DISTINCT ( Name )) T4
      , COUNT(DISTINCT ( COALESCE(NULL, '') )) T5
      , @@ROWCOUNT T6
    FROM TEST
    GROUP BY Name;
DROP TABLE TEST;

结果集中没有'JAMES'？ (大写)

请说说这是怎么排除的

预期为 Null,james,JAMES,eric

Answer 1

您需要将 Name 列排序规则更改为 Latin1_General_CS_AS，它区分大小写

SELECT NAME COLLATE Latin1_General_CS_AS,
       Count(NAME)                            AS T1,
       Count(COALESCE(NULL, ''))              T2,
       Count(Isnull(NAME, NULL))              T3,
       Count(DISTINCT ( Name ))               T4,
       Count(DISTINCT ( COALESCE(NULL, '') )) T5,
       @@ROWCOUNT                             T6
FROM   TEST
GROUP  BY Name COLLATE Latin1_General_CS_AS;