数据定义语言（DDL）：

IF OBJECT_ID('tempdb..#Tab2And3Pop')  is not null
DROP TABLE #Tab2And3Pop;

SELECT     CAST(115704   AS INT) AS ConflictNoticeID
     , CAST(16003573 AS INT) AS MerchantID
     , CAST(13       AS INT) AS WebUserID
     , CAST(NULL     AS bit) AS CNID_RecordCount
INTO #Tab2And3Pop
UNION SELECT 115704, 16003573, 356459, NULL
UNION SELECT 129110, 6140405 , 28    , NULL
UNION SELECT 129110, 6140405 , 251787, NULL
UNION SELECT 134799, 14868652, 30    , NULL
UNION SELECT 134799, 14868652, 251787, NULL;

--------------------------------------

;with RecordCount AS (
    select ConflictNoticeID, COUNT(*) AS CountOfCNID
    from #Tab2And3Pop
    GROUP BY ConflictNoticeID
)
update p
SET p.CNID_RecordCount = rc.CountOfCNID
--select *
from #Tab2And3Pop p
    JOIN RecordCount RC on rc.ConflictNoticeID = p.ConflictNoticeID

我本以为临时表中的 CNID_RecordCount 会被更新为2，但结果全是1：

解决方案

问题在于 CAST(NULL AS bit) AS CNID_RecordCount

SELECT     CAST(115704   AS INT) AS ConflictNoticeID
     , CAST(16003573 AS INT) AS MerchantID
     , CAST(13       AS INT) AS WebUserID
     , CAST(NULL     AS bit) AS CNID_RecordCount   --   here
INTO #Tab2And3Pop

应该是：CAST(NULL AS INT)，以避免隐式转换。

原理： 位数据类型:

将任意非零值提升为1。