需求背景：在任务管理系统中，有任务详情表，每个任务下又分子任务节点，每个任务节点都有具体的跟进日期，包括开始时间结束时间，每天的任务完成进度。 有这样一个需求：在任务管理系统中根据任务节点id，展示每天的进度情况。 因为每个任务的时间范围不一样，所以需要用到动态的行转列，将时间周期作为动态的列，进度做为列的值。 任务节点表： quest_node_id：任务子节点id，follow_date:跟进日期 ，grade:进度百分比。 在mysql中创建存储过程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
BEGIN SET @EE=''; SELECT b.* into @EE from ( SELECT @EE:=CONCAT(@EE,'SUM(IF(follow_date=\'',follow_date,'\'',',grade,0)) AS ',follow_date,',') col FROM (SELECT DISTINCT follow_date FROM quest_follow_copy) A) b ORDER BY b.col desc limit 0,1 ; SET @QQ=CONCAT('SELECT quest_follow_copy.quest_node_id AS columnA,',LEFT(@EE,LENGTH(@EE)-1),' FROM quest_follow_copy GROUP BY quest_node_id'); PREPARE stmt2 FROM @QQ; EXECUTE stmt2; END |
注意：我发现follow_date的日期格式（如2018-08-10）会报错，存储过程执行不了，并且数字开头也不可以，所以我把日期改成了varchar类型，并在值里加了前缀。 执行结果如下： 某个节点在某天没有值则补零。 参考文档：mysql 动态行转列 from:https://blog.csdn.net/xiaoxiangzi520/article/details/81480477
View Details创建数据库、表
|
1 2 3 4 5 6 7 8 |
create database tests; use tests; create table t_score( id int primary key auto_increment, name varchar(20) not null, #名字 Subject varchar(10) not null, #科目 Fraction double default 0 #分数 ); |
添加数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
INSERT INTO `t_score`(name,Subject,Fraction) VALUES ('王海', '语文', 86), ('王海', '数学', 83), ('王海', '英语', 93), ('陶俊', '语文', 88), ('陶俊', '数学', 84), ('陶俊', '英语', 94), ('刘可', '语文', 80), ('刘可', '数学', 86), ('刘可', '英语', 88), ('李春', '语文', 89), ('李春', '数学', 80), ('李春', '英语', 87); |
方式一:使用if
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select name as 名字 , sum(if(Subject='语文',Fraction,0)) as 语文, sum(if(Subject='数学',Fraction,0))as 数学, sum(if(Subject='英语',Fraction,0))as 英语, round(AVG(Fraction),2) as 平均分, SUM(Fraction) as 总分 from t_score group by name union select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from( select 'TOTAL' as name, sum(if(Subject='语文',Fraction,0)) as 语文, sum(if(Subject='数学',Fraction,0))as 数学, sum(if(Subject='英语',Fraction,0))as 英语, SUM(Fraction) as 总分 from t_score group by Subject )t |
方式二:使用case
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select name as Name, sum(case when Subject = '语文' then Fraction end) as Chinese, sum(case when Subject = '数学' then Fraction end) as Math, sum(case when Subject = '英语' then Fraction end) as English, sum(fraction)as score from t_score group by name UNION ALL select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from( select 'TOTAL' as name, sum(case when Subject = '语文' then Fraction end) as Chinese, sum(case when Subject = '数学' then Fraction end) as Math, sum(case when Subject = '英语' then Fraction end) as English, sum(fraction)as score from t_score group by Subject)t |
方法三: with rollup
|
1 2 3 4 5 6 7 |
select ifnull(name,'TOll') name, sum(if(Subject='语文',Fraction,0)) as 语文, sum(if(Subject='英语',Fraction,0)) as 英语, sum(if(Subject='数学',Fraction,0))as 数学, sum(Fraction) 总分 from t_score group by name with rollup |
from:https://www.cnblogs.com/weibanggang/p/9679301.html
View Details|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
<el-table :data="tableData" style="width: 100%"> <el-table-column v-for="col in cols" :prop="col.prop" :label="col.label" > </el-table-column> </el-table> <el-button raw-type="button" @click="addCol"> 添加一列 </el-button> data() { return { tableData: [{ date: '2016-05-02', name: '王小虎', address: '上海市普陀区金沙江路 1518 弄' }, { date: '2016-05-04', name: '王小虎', address: '上海市普陀区金沙江路 1517 弄' }, { date: '2016-05-01', name: '王小虎', address: '上海市普陀区金沙江路 1519 弄' }, { date: '2016-05-03', name: '王小虎', address: '上海市普陀区金沙江路 1516 弄' }], cols: [ {prop: 'date', label: '日期'}, {prop: 'name', label: '姓名'}, ] } }, methods: { addCol(){ this.cols.push({prop: 'address', label: '地址'}) } |
from:https://blog.csdn.net/baidu_34692401/article/details/83348130
View Details