sqlite3：为列B中的每个唯一实例计算列A中的实例数

Question

假设我设置了一个sqlite表：

    ColumnA | ColumnB
   ---------|----------
       A    |    One
       B    |    One
       C    |    Two
       D    |    Three
       E    |    Two
       F    |    Three
       G    |    Three

是否有一个查询可以查找a列中具有B列中相同实例的实例数？或者使用脚本从行中提取（pythonsqlite3）会更好吗？你知道吗

例如

query("One") = 2
query("Two") = 2
query("Three") = 3

谢谢

Answer 1

这很容易由sqlite3本身实现。你知道吗

$ sqlite3 mydb.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .databases
seq  name             file                                                      
 -         -                               
0    main             /home/ziya/mydb.db                                        
sqlite> create table two_column( col_a char(5), col_b varchar(20) );
sqlite> insert into two_column values('A', 'One');
sqlite> insert into two_column values('B', 'One');
sqlite> insert into two_column values('C', 'Two');
sqlite> insert into two_column values('D', 'Three');
sqlite> insert into two_column values('E', 'Two');
sqlite> insert into two_column values('F', 'Three');
sqlite> insert into two_column values('G', 'Three');
sqlite> select * from two_column;
A|One
B|One
C|Two
D|Three
E|Two
F|Three
G|Three
sqlite> select count(*) from two_column where col_b = 'One';
2
sqlite> select count(*) from two_column where col_b = 'Two';
2
sqlite> select count(*) from two_column where col_b = 'Three';
3

如果你对python还满意

>>> import sqlite3
>>> c = sqlite3.connect("mydb.db")
>>> cur = c.execute("SELECT COUNT(col_b) FROM two_column WHERE col_b = '{}' ".format('A'))
>>> [r for r in cur]
[(0,)]

您可以使用上述语句轻松地生成函数。你知道吗

Answer 2

如果要同时获取所有计数，可以使用分组：

SELECT ColumnB, count(*)
FROM MyTable
GROUP BY ColumnB;