mysql的 函数 group_concat ， substring_index 的使用以及 分库分表操作

参考：http://www.tuicool.com/articles/eQ3EVv

mysql中没有类似oracle和postgreSQL的 OVER(PARTITION BY)功能. 那么如何在MYSQL中搞定分组聚合的查询呢

先说结论: 利用 group_concat + substr等函数处理

例如: 订单表一张, 只保留关键字段

|id user_id money create_time
1 1 50 1420520000
2 1 100 1420520010
3 2 100 1420520020
4 2 200 1420520030

业务: 查找每个用户的最近一笔消费金额

单纯使用group by user_id, 只能按user_id 将money进行聚合, 是无法将最近一单的金额筛选出来的, 只能满足这些需求, 例如: 每个用户的总消费金额 sum(money), 最大消费金额 max(money), 消费次数count(1) 等

但是我们有一个group_concat可以用, 思路如下:

1. 查找出符合条件的记录, 按user_id asc, create_time desc 排序;

mysql> select*from orders1 order by user_id asc,create_time desc;
+----+---------+-------+-------------+
| id | user_id | money | create_time |
+----+---------+-------+-------------+
|  2 |       1 |   100 | 14205220010 |
|  1 |       1 |    50 |  1420520000 |
|  4 |       2 |   200 |  1420520030 |
|  3 |       2 |   100 |  1420520020 |
+----+---------+-------+-------------+
4 rows in set (0.00 sec)

2. 将(1)中记录按user_id分组, group_concat(money);

mysql> select user_id,group_concat(money) as Smoney from orders1 group by user_i
d;
+---------+---------+
| user_id | Smoney  |
+---------+---------+
|       1 | 50,100  |
|       2 | 100,200 |
+---------+---------+
2 rows in set (0.00 sec)



mysql> select user_id,group_concat(money) from orders1 group by user_id;
+---------+---------------------+
| user_id | group_concat(money) |
+---------+---------------------+
|       1 | 50,100              |
|       2 | 100,200             |
+---------+---------------------+
2 rows in set (0.00 sec)

3. 这时, 如果用户有多个消费记录, 就会按照时间顺序排列好, 再利用 subString_index 函数进行切分即可

完整SQL:

mysql> select user_id,substring_index(group_concat(money),',',1) from orders1 gr
oup by user_id;
+---------+--------------------------------------------+
| user_id | substring_index(group_concat(money),',',1) |
+---------+--------------------------------------------+
|       1 | 50                                         |
|       2 | 100                                        |
+---------+--------------------------------------------+
2 rows in set (0.04 sec)

利用这个方案, 以下类似业务需求都可以这么做, 如:

1. 查找每个用户过去10个的登陆IP

2. 查找每个班级中总分最高的两个人

----------- 分割线 ------- ：

分库分表操作 ：

见 该文章 ：mysql数据库的

http://www.jb51.net/article/36272.htm

完整的操作过程如下：

mysql> create table orders1(id int auto_increment primary key,user_id int,money
int,create_time timestamp);
Query OK, 0 rows affected (0.31 sec)

mysql> alter table orders1 modify create_time bigint;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> insert into orders1 values(0,1,50,1420520000),(0,1,100,14205220010),(0,2,
100,1420520020),(0,2,200,1420520030);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select*from orders1;
+----+---------+-------+-------------+
| id | user_id | money | create_time |
+----+---------+-------+-------------+
|  1 |       1 |    50 |  1420520000 |
|  2 |       1 |   100 | 14205220010 |
|  3 |       2 |   100 |  1420520020 |
|  4 |       2 |   200 |  1420520030 |
+----+---------+-------+-------------+
4 rows in set (0.00 sec)



mysql> select*from orders1 order by user_id asc,create_time desc;
+----+---------+-------+-------------+
| id | user_id | money | create_time |
+----+---------+-------+-------------+
|  2 |       1 |   100 | 14205220010 |
|  1 |       1 |    50 |  1420520000 |
|  4 |       2 |   200 |  1420520030 |
|  3 |       2 |   100 |  1420520020 |
+----+---------+-------+-------------+
4 rows in set (0.00 sec)


mysql> select user_id, money,create_time from orders1 order by user_id asc , cre
ate_time desc
    -> ;
+---------+-------+-------------+
| user_id | money | create_time |
+---------+-------+-------------+
|       1 |   100 | 14205220010 |
|       1 |    50 |  1420520000 |
|       2 |   200 |  1420520030 |
|       2 |   100 |  1420520020 |
+---------+-------+-------------+
4 rows in set (0.00 sec)


mysql> select user_id,group_concat(money) as Smoney from orders1 group by user_i
d;
+---------+---------+
| user_id | Smoney  |
+---------+---------+
|       1 | 50,100  |
|       2 | 100,200 |
+---------+---------+
2 rows in set (0.00 sec)



mysql> select user_id,group_concat(money) from orders1 group by user_id;
+---------+---------------------+
| user_id | group_concat(money) |
+---------+---------------------+
|       1 | 50,100              |
|       2 | 100,200             |
+---------+---------------------+
2 rows in set (0.00 sec)


mysql> select ord.user_id, group_concat(ord.money)
    -> from orders1 ord
    -> where ord.user_id > 0 and create_time > 0
    -> group by user_id, create_time
    ->  order by user_id asc , create_time desc;
+---------+-------------------------+
| user_id | group_concat(ord.money) |
+---------+-------------------------+
|       1 | 100                     |
|       1 | 50                      |
|       2 | 200                     |
|       2 | 100                     |
+---------+-------------------------+
4 rows in set (0.07 sec)



mysql> select user_id,group_concat(money) from orders1 group by user_id;
+---------+---------------------+
| user_id | group_concat(money) |
+---------+---------------------+
|       1 | 50,100              |
|       2 | 100,200             |
+---------+---------------------+
2 rows in set (0.69 sec)

mysql> select user_id,substring_index(group_concat(money),',',1) from orders1 gr
oup by user_id;
+---------+--------------------------------------------+
| user_id | substring_index(group_concat(money),',',1) |
+---------+--------------------------------------------+
|       1 | 50                                         |
|       2 | 100                                        |
+---------+--------------------------------------------+
2 rows in set (0.04 sec)

mysql>