» 2013 » January » 06 の記事

先要create function

DELIMITER //
CREATE FUNCTION bits_find_N1(bits BIGINT, trait BIGINT)
  RETURNS BOOL
  BEGIN
    WHILE bits <> 0 DO
      IF ((bits & trait) = trait) THEN
        RETURN TRUE;
      END IF;
      SET bits = bits >> 1;
    END WHILE;
    RETURN FALSE;
  END//
  DELIMITER ;

mysql statement

SELECT accountname AS bit FROM tablename
WHERE log_date BETWEEN '2012-12-30 00:00:00' AND '2013-01-03 23:59:59'
GROUP BY uid
HAVING bits_find_N1(BIT_OR(1 << datediff(log_date, '2012-12-30')),
b'11111') IS TRUE;

如果某column 數目要到一定數目

SELECT accountname AS bit, sum(cardamount) FROM tablename
WHERE log_date BETWEEN '2012-12-30 00:00:00' AND '2013-01-03 23:59:59'
GROUP BY uid
HAVING bits_find_N1(BIT_OR(1 << datediff(log_date, '2012-12-30')),
b'11111') IS TRUE and sum(cardamount)>=1200;

詳細可參考

http://www.mysqlops.com/2012/03/06/an_interesting_query.html