SQL 同一字段如何分别统计值的总数?

发布网友发布时间：2023-07-05 01:23

共1个回答

热心网友时间：2024-10-18 06:37

CREATE TABLE #t (\x0d\x0a a varchar(4),\x0d\x0a b int\x0d\x0a)\x0d\x0ago\x0d\x0a\x0d\x0aINSERT INTO #T\x0d\x0a SELECT '甲', 0 UNION ALL\x0d\x0a SELECT '乙', 0 UNION ALL\x0d\x0a SELECT '丙', 1 UNION ALL\x0d\x0a SELECT '丁', 0 UNION ALL\x0d\x0a SELECT '甲', 1 UNION ALL\x0d\x0a SELECT '乙', 1 UNION ALL\x0d\x0a SELECT '丙', 0 UNION ALL\x0d\x0a SELECT '丁', 0\x0d\x0aGO\x0d\x0a\x0d\x0a办法1:\x0d\x0a\x0d\x0aSELECT\x0d\x0a a,\x0d\x0a SUM( CASE WHEN b=1 THEN 1 ELSE 0 END ) AS [为1统计],\x0d\x0a SUM( CASE WHEN b=0 THEN 1 ELSE 0 END ) AS [为0统计]\x0d\x0aFROM\x0d\x0a #t\x0d\x0aGROUP BY\x0d\x0a a\x0d\x0aGO\x0d\x0a\x0d\x0a办法2: (SQL Server 2005 以上可用)\x0d\x0a\x0d\x0aSELECT\x0d\x0a a,\x0d\x0a tmp.[1] AS [为1统计],\x0d\x0a tmp.[0] AS [为0统计]\x0d\x0aFROM\x0d\x0a #t\x0d\x0aPIVOT( \x0d\x0a COUNT(b)\x0d\x0a FOR b IN ([0], [1])\x0d\x0a) tmp\x0d\x0ago