问题描述

sql编写问题，统计一列里面所有不同值的记录数

字段里共包含5种值，“a”，“b”,"c","d","e",请问怎样统计这列字段每种值的记录数？
较为简便的写法是什么？有现成的函数吗？

如表 T 字段 f1，共有6条记录，

              a
                                a
                                b
                                c
                                d
                                e
希望获得结果 count_a, count_b, count_c, count_d, count_e
                    -------------------------------------------------
                                        2            1            1             1            1

解决方案

select count(a) as count_a,count(b) as count_b,count(c) as count_c,count(d) as count_d,count(e) as count_c from 表名 group by 字段名

解决方案二：

SQL Sever

/* 测试数据
WITH T(f1)AS(
    SELECT 'a' UNION ALL
    SELECT 'a' UNION ALL
    SELECT 'b' UNION ALL
    SELECT 'c' UNION ALL
    SELECT 'd' UNION ALL
    SELECT 'e'
) */
SELECT [a] AS count_a,
       [b] AS count_b,
       [c] AS count_c,
       [d] AS count_d,
       [e] AS count_e
  FROM t
 PIVOT (COUNT(f1)
        FOR f1 IN ([a],[b],[c],[d],[e])
       ) p

结果

count_a     count_b     count_c     count_d     count_e
----------- ----------- ----------- ----------- -----------
          2           1           1           1           1

解决方案三：

group by 分组，然后count统计

解决方案四：

一楼正解。

 SELECT COUNT(a) as count_a,
                COUNT(b) as count_b,
                    COUNT(c) as count_c,
                    COUNT(d) as count_d,
                    COUNT(e) as count_c
FROM 表名
GROUP BY  列名

binggo！