|
发表于 2013-11-1 13:22:17
|
显示全部楼层
本帖最后由 fengwei 于 2013-11-1 15:39 编辑
例如你的数据结构是这样的
+----+------+----------+------------+
| id | name | worktime | date |
+----+------+----------+------------+
| 1 | 张三 | 8 | 2013-11-04 |
| 2 | 张三 | 8 | 2013-11-05 |
| 3 | 张三 | 8 | 2013-11-06 |
| 4 | 张三 | 8 | 2013-11-07 |
| 5 | 张三 | 8 | 2013-11-08 |
| 6 | 张三 | 8 | 2013-11-09 |
| 7 | 张三 | 8 | 2013-11-10 |
+----+------+----------+------------+
,那么这个语句 select name,sum(case dayofweek(date) when '1' then worktime end) as worktime1,sum(case dayofweek(date) when '2' then worktime end) as worktime2,sum(case dayofweek(date) when '3' then worktime end) as worktime3,sum(case dayofweek(date) when '4' then worktime end) as worktime4,sum(case dayofweek(date) when '5' then worktime end) as worktime5,sum(case dayofweek(date) when '6' then worktime end) as worktime6,sum(case dayofweek(date) when '7' then worktime end) as worktime7 from work where date between '2013-11-4' and '2013-11-11' group by name;就可以实现如下的查询返回结果
+------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| name | worktime1 | worktime2 | worktime3 | worktime4 | worktime5 | worktime6 | worktime7 |
+------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 张三 | 8 | 8 | 8 | 8 | 8 | 8 | 8 |
+------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
主要是使用这个语句实现胖瘦转换sum(case dayofweek(date) when '1' then worktime end)
当日期为某一天时,返回该天工作时间。其中dayofweek()是mysql的函数 返回日期的在一星期中的索引,返回值是{1,2,3,4,5,6,7}中的一个。
|
|