背景
从数据库导出数据给运营人员看,类型数字要用中文显示代表的含义,内容一对多的数据,如内容分类可能有三个,需要在一行显示,用,号隔开。
示例
语法
1. group_concat函数
基本查询
mysql> select * from stu1;
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)
以id分组,把name字段的值打印在一行,逗号分隔(默认)
mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)
2.case when语句
基本查询:select idea_type , idea_id from t_ideas
其中1代表提问,2代表回答。
case when 语句:
select idea_id, idea_type, (case idea_type
WHEN 1 then '提问'
WHEN 2 then '回答'
WHEN 3 then '讨论'
else '未知' end ) '类型' from t_ideas
结果:
完整的语法:
select ti.idea_id as '内容id', tio.browses as '浏览量',
(case ti.idea_type WHEN 1 then '提问'
WHEN 2 then '回答'
WHEN 3 then '讨论'
else '未知' end ) '类型',
tc.tcc as '分类' , date_format(ti.create_time,'%Y-%m-%d') as '创建时间'
from t_ideas as ti
left join t_idea_other as tio on ti.idea_id = tio.idea_id
left join (select tic.idea_id, group_concat(tc.category) as tcc
from t_idea_category as tic left join t_categorys as tc on tic.category_id = tc.category_id
group by tic.idea_id) as tc on tc.idea_id = tio.idea_id
where ti.create_time BETWEEN "2019-10-01" and "2019-11-30" group by tio.browses desc