mysql 二次 聚合_MySQL之聚合

EP:巧妙地过滤掉重复的日期

example:使用bit_or() bit_count()巧妙地过滤掉重复的日期:

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, 23),(2000, 2, 23);

+------+-------+------+

| year | month | day |

+------+-------+------+

| 2000 | 01 | 01 |

| 2000 | 01 | 20 |

| 2000 | 01 | 30 |

| 2000 | 02 | 02 |

| 2000 | 02 | 23 |

| 2000 | 02 | 23 |

+------+-------+------+

#计算天数去除重复天数

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 |

+------+-------+------+

*****-GROUP_CONCAT

select group_concat(DISTINCT b order by b desc separator ':') FROM z group by a;