select a,b,count(1) fom test group by a,b. 这里的count(1)帮忙详细解释一下，谢谢

Question

select a,b,count(1) fom test group by a,b.    这里的count(1)帮忙详细解释一下，谢谢帮帮忙，不明白为什么会计算出重复的行数！

Answer 1

如果A,B都不是空,等同于select a , b , count(*) fom test group by a , b

Answer 2

count(1)和group by放在一起，用来计数select各列组合的重复记录数：SQL> select * from test1;
         1          2          3          4
         1          2          3          4
         2          3          4          5
         2          3          4          6
         6          2          3          7
         1          7          7          1
         4          5          5          8
         1          5          5          9
         2          7          7          0
         9          3          4          9
         0          2          6          7已选择11行。SQL> select id1,id2,id3,id4,count(1) from test1 group by id1,id2,id3,id4;
         2          3          4          6          1
         6          2          3          7          1
         2          7          7          0          1
         9          3          4          9          1
         1          7          7          1          1
         0          2          6          7          1
         1          2          3          4          2
         4          5          5          8          1
         1          5          5          9          1
         2          3          4          5          1已选择10行。  1* select id1,id2,id3,count(1) from test1 group by id1,id2,id3
SQL> /
         2          3          4          2
         9          3          4          1
         0          2          6          1
         1          7          7          1
         4          5          5          1
         1          2          3          2
         6          2          3          1
         2          7          7          1
         1          5          5          1已选择9行。

Answer 3

我测试count(*),count(1),count(主键),结果是一样的，效率也差不多。

Answer 4

我测试了下：
count(1),count(*),count(8),count(100),没什么区别

Answer 5

SQL> select * from t_c;        ID NAME     EMAIL
---------- -------- --------
         1 phenix   note1
         2 phenix   note1
         3 phenix   note1
         4
         5
SQL> select name, email,count(100) from t_c group by name,email;NAME     EMAIL    COUNT(100)
-------- -------- ----------
                           2
phenix   note1             3
SQL> select name, email,count(8) from t_c group by name,email;NAME     EMAIL      COUNT(8)
-------- -------- ----------
                           2
phenix   note1             3
SQL> select name, email,count(*) from t_c group by name,email;NAME     EMAIL      COUNT(*)
-------- -------- ----------
                           2
phenix   note1             3结果一样。

Answer 6

不考虑空值影响的时候，count(*)最普遍，也是效率最高的。
count(pk)最保险，不过写着麻烦。