MySql-BIT_COUNT()&BIT_OR()
MySql---BIT_COUNT()&BIT_OR()完全转载:http://msh24.iteye.com/blog/1136449Java代码??CREATE?TABLE?t1?(
MySql---BIT_COUNT()&BIT_OR()
完全转载:http://msh24.iteye.com/blog/1136449Java代码??
- CREATE?TABLE?t1?(year?YEAR(4),?month?INT(2)?UNSIGNED?ZEROFILL,??
- ?????????????day?INT(2)?UNSIGNED?ZEROFILL);??
- INSERT?INTO?t1?VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),??
- ????????????(2000,2,3),(2000,2,3);??
- 示例表中含有代表用户访问网页的年-月-日值。可以使用以下查询来确定每个月的访问天数:??
- ??
- SELECT?year,month,BIT_COUNT(BIT_OR(1<<day))?AS?days?FROM?t1??
- ???????GROUP?BY?year,month;??
- 将返回:??
- ??
- +------+-------+------+??
- |?year?|?month?|?days?|??
- +------+-------+------+??
- |?2000?|????01?|????3?|??
- |?2000?|????02?|????2?|??
- +------+-------+------+??
- 该查询计算了在表中按年/月组合的不同天数,可以自动去除重复的询问。??
<<位运算 使1左移了day位。?
1<<2=0100? 1<<3=1000?
bit_or:相同值or运算后值一样,最后把不一样的值加起来。?
1000 or 1000 or 0100 = 1100?
bit_count:统计被制1的个数。?
bit_count(1100) = 2;?
说白了意思就是统计被制1的个数。?
这个统计还可以这样写:select year, month, count(distinct day) from t1 group by year, month;