请教如何统计所有记录中1个个数？

光米

例如：


userid   pid   value
| 10  |    2 | 000000000000000000000000000101
| 11  |    3 | 111000011000000000000000000011

光米

select length(field1)-length(replace(field1,'#','')) from test;

光米

select sum(length(field1)-length(replace(field1,'1','')) )from test;

909413335

1. mysql> select * from tbl;
   
2. +--------+------+--------------------------------+
   
3. | userid | pid  | value                          |
   
4. +--------+------+--------------------------------+
   
5. |     10 |    2 | 000000000000000000000000000101 |
   
6. |     11 |    3 | 111000011000000000000000000011 |
   
7. +--------+------+--------------------------------+
   
8. 2 rows in set (0.00 sec)
   
9. 
   
10. mysql> select userid,pid,length(replace(value,'0','')) as len from tbl;
    
11. +--------+------+------+
    
12. | userid | pid  | len  |
    
13. +--------+------+------+
    
14. |     10 |    2 |    2 |
    
15. |     11 |    3 |    7 |
    
16. +--------+------+------+
    
17. 2 rows in set (0.00 sec)
    
18. 
    
19. mysql>

复制代码

fly020212

楼上好办法