SQL/MySQL/Function/BIT Count

Материал из SQL эксперт
Перейти к: навигация, поиск

Use BIT_COUNT function

   <source lang="sql">

/* mysql> /* how many different days in each month these visits occur mysql> */ mysql> SELECT year, month, BIT_COUNT(BIT_OR(1<<day)) AS days FROM timeTable

   ->        GROUP BY year,month;

+------+-------+------+ | year | month | days | +------+-------+------+ | 2001 | 01 | 1 | | 2002 | 06 | 1 | | 2003 | 05 | 1 | | 2004 | 02 | 1 | | 2005 | 04 | 1 | | 2006 | 03 | 1 | +------+-------+------+ 6 rows in set (0.02 sec)

  • /

Drop table timeTable; CREATE TABLE timeTable (

   year YEAR(4), 
   month INT(2) UNSIGNED ZEROFILL,
   day INT(2) UNSIGNED ZEROFILL

); INSERT INTO timeTable VALUES(2001,1,1),

                           (2002,6,20),
                           (2003,5,30),
                           (2004,2,2),
                           (2005,4,23),
                           (2006,3,23);

/* how many different days in each month these visits occur

*/

SELECT year, month, BIT_COUNT(BIT_OR(1<<day)) AS days FROM timeTable

      GROUP BY year,month;
          
      </source>