MySQL以字段进行分组并分页

  • 原始数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
> SELECT * FROM `app_language`;
+----+--------+-------+--------------+--------------------------------------+---------------------+---------------------+
| id | app_id | lang | lang_key | lang_value | create_time | update_time |
+----+--------+-------+--------------+--------------------------------------+---------------------+---------------------+
| 1 | 1 | en-US | home.welcome | Welcome | 2020-05-06 08:34:44 | 2020-05-06 08:34:44 |
| 2 | 1 | zh-CN | home.hello | 你好 | 2020-05-06 11:58:39 | 2020-05-06 10:32:55 |
| 3 | 1 | th-TH | home.test | ทดสอบ | 2020-05-06 11:41:36 | 2020-05-06 11:32:13 |
| 4 | 1 | zh-CN | home.test | 测试 | 2020-05-06 11:40:40 | 2020-05-06 11:32:13 |
| 5 | 1 | en-US | home.test | Test | 2020-05-06 11:40:45 | 2020-05-06 11:32:13 |
| 6 | 1 | th-TH | home.welcome | ยินดีต้อนรับ | 2020-05-06 11:41:23 | 2020-05-06 11:32:52 |
| 7 | 1 | th-TH | home.hello | สวัสดี | 2020-05-06 11:59:05 | 2020-05-06 11:47:22 |
| 8 | 1 | zh-CN | home.welcome | 欢迎 | 2020-05-06 11:48:55 | 2020-05-06 11:47:22 |
| 9 | 1 | en-US | home.hello | Hello | 2020-05-06 11:59:14 | 2020-05-06 11:47:22 |
+----+--------+-------+--------------+--------------------------------------+---------------------+---------------------+
9 rows in set (0.00 sec)
  • lang_key进行分组(不损失数据,直接group by会损失数据,拼接的数据可在代码中处理)
1
2
3
4
5
6
7
8
9
mysql> select GROUP_CONCAT(lang) as lang, GROUP_CONCAT(lang_value) as lang_value, lang_key, create_time from app_language where app_id = 1 group by lang_key order by create_time desc;
+-------------------+-----------------------------------------------------+--------------+---------------------+
| lang | lang_value | lang_key | create_time |
+-------------------+-----------------------------------------------------+--------------+---------------------+
| zh-CN,th-TH,en-US | 你好,สวัสดี,Hello | home.hello | 2020-05-06 11:58:39 |
| th-TH,zh-CN,en-US | ทดสอบ,测试,Test | home.test | 2020-05-06 11:41:36 |
| en-US,th-TH,zh-CN | Welcome,ยินดีต้อนรับ,欢迎 | home.welcome | 2020-05-06 08:34:44 |
+-------------------+-----------------------------------------------------+--------------+---------------------+
3 rows in set (0.01 sec)
  • lang_key进行分组,并分页
1
2
3
4
5
6
7
8
mysql> select GROUP_CONCAT(lang) as lang, GROUP_CONCAT(lang_value) as lang_value, lang_key, create_time from app_language where app_id = 1 group by lang_key order by create_time desc limit 0,2;
+-------------------+---------------------------------+------------+---------------------+
| lang | lang_value | lang_key | create_time |
+-------------------+---------------------------------+------------+---------------------+
| zh-CN,th-TH,en-US | 你好,สวัสดี,Hello | home.hello | 2020-05-06 11:58:39 |
| th-TH,zh-CN,en-US | ทดสอบ,测试,Test | home.test | 2020-05-06 11:41:36 |
+-------------------+---------------------------------+------------+---------------------+
2 rows in set (0.01 sec)

Powered by AppBlog.CN     浙ICP备14037229号

Copyright © 2012 - 2020 APP开发技术博客 All Rights Reserved.

访客数 : | 访问量 :