mysql - mysql如何对多行进行操作

Question

我有一个轮类时间表，供员工每月轮类。该表一共有34个字段。

表格标题是:

EmpNo |Month|year|Day1|Day2|Day3|Day4|Day5|Day6|Day7|Day8|Day9|Day10|Day11|Day12|Day13|Day14|Day15|Day16|Day17|Day18|Day19|Day20|Day21|Day22|Day23|Day24|Day25|Day26|Day27|Day28|Day29|Day30|Day31

表格数据:

101|06|2018|A1|A1|A1|H|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1|A1|H|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1
102|06|2018|A1|A1|H|A1|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1|A1|H|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1
103|06|2018|A1|A1|A1|A1|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1|A1|H|A1|WO|WO|A1|A1|A1|A1|A1|WO|WO|A1|A1

此处A1是类次代码，WO是周末休息日，H是假期。

我需要计算员工每月的休息周和假期总数。

Answer 1

你可以这样做 How to count items in comma separated list MySQL其中长度和替换用于计算出现的次数。在你的情况下，它有点复杂(但不多)，你必须连接日期并满足空值 - 我建议每天添加一个 ifnull 测试，并可能替换一个虚拟值“x”。

drop table if exists t;
create table t(empid varchar(3),month varchar(2), year varchar(4),
day1 varchar(2), day2 varchar(2) ,day3 varchar(2),day4 varchar(2), day5 varchar(2), day6 varchar(2),
day7 varchar(2), day8 varchar(2) ,day9 varchar(2),day10 varchar(2), day11 varchar(2), day12 varchar(2),
day13 varchar(2), day14 varchar(2), day15 varchar(2),day16 varchar(2), day17 varchar(2), day18 varchar(2),
day19 varchar(2), day20 varchar(2), day21 varchar(2),day22 varchar(2), day23 varchar(2), day24 varchar(2),
day25 varchar(2), day26 varchar(2), day27 varchar(2),day28 varchar(2), day29 varchar(2), day30 varchar(2));
#day31 varchar(2));
insert into t values
('101','06','2018','A1','A1','A1','H','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1','A1','H','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1'),
('102','06','2018','A1','A1','H','A1','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1','A1','H','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1'),
('103','06','2018','A1','A1','A1','A1','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1','A1','H','A1','WO','WO','A1','A1','A1','A1','A1','WO','WO','A1','A1');

select empid,month,year,
         length(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30)) len,
         length(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30)) -
            length(replace(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30),
                            'H','')) h,
                ( length(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30)) -
            length(replace(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30),
                            'WO',''))
                    ) /2  wo,

                 length(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30)) -
            length(replace(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30),
                            'H','')) +
                ( length(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30)) -
            length(replace(concat(day1,day2,day3,day4,day5,day6,day7,day8,day9,day10,
                            day11,day12,day13,day14,day15,day16,day17,day18,day19,day20,
                            day21,day22,day23,day24,day25,day26,day27,day28,day29,day30),
                            'WO',''))
                    ) /2   off
from t;

+-------+-------+------+------+------+--------+---------+
| empid | month | year | len  | h    | wo     | off     |
+-------+-------+------+------+------+--------+---------+
| 101   | 06    | 2018 |   58 |    2 | 8.0000 | 10.0000 |
| 102   | 06    | 2018 |   58 |    2 | 8.0000 | 10.0000 |
| 103   | 06    | 2018 |   59 |    1 | 8.0000 |  9.0000 |
+-------+-------+------+------+------+--------+---------+
3 rows in set (0.00 sec)