|
本帖最后由 周大胖子 于 2019-5-21 16:57 编辑
============================= 统计报表 ==============================================
统计:目标 在 财务的表中 得出 一年内 每个月 所得的收入总和
已知: r_time 为时间类型 y-m-d , collect 为单次操作 所收金额 类型 decimal
研究初始语句 : select r_time, sum(collect) as month_all from oa_money_record group by r_time
得出语句 看尾部
1. 统计一个月 的所有钱
【错误】select sum('collect') as 'month_all' from 'oa_money_record' where 'r_time' > '2017-03-00' and 'r_time' < '2017-04-00';
datetime /time /date 类型 要加''
[正确] select sum(collect) as month_all from oa_money_record where r_time > '2019-03-00' and r_time < '2019-04-00';
2.关于1的改动
2.1 输出要两个值[输出当前月份]
select r_time,sum(collect) as month_all from oa_money_record where r_time > '2019-03-00' and r_time < '2019-04-00';
2.2 mysql截取利用left(str, length)
select left(r_time,7) as month,sum(collect) as month_all from oa_money_record where r_time > '2019-03-00' and r_time < '2019-04-00';
2.3 尝试分组 【正确运行 可是 出现r_time 同月的四个值】
select left(r_time,7) as month,sum(collect) as month_all from oa_money_record GROUP BY r_time HAVING r_time > '2019-03-00' and r_time < '2019-04-00';
2.4 单分组尝试
select left(r_time,7) as month,sum(collect) as month_all from oa_money_record GROUP BY r_time HAVING r_time > '2019-03-00' and r_time < '2019-04-00';
2.5 尝试where 和 group 一块用 [这个只有型 其实能用 因为没有 0号 这一天]
select left(r_time,7) as month,sum(collect) as month_all from oa_money_record where r_time between '2019-01-00' and '2019-02-00'
or r_time between '2019-02-00' and '2019-03-00'
or r_time between '2019-03-00' and '2019-04-00'
or r_time between '2019-04-00' and '2019-05-00'
or r_time between '2019-05-00' and '2019-06-00'
or r_time between '2019-06-00' and '2019-07-00'
or r_time between '2019-07-00' and '2019-08-00'
or r_time between '2019-08-00' and '2019-09-00'
or r_time between '2019-09-00' and '2019-10-00'
or r_time between '2019-10-00' and '2019-11-00'
or r_time between '2019-11-00' and '2019-12-00'
or r_time between '2019-12-00' and '2020-01-00'
GROUP BY month ;
select left(r_time,7) as month,sum(collect) as month_all from oa_money_record where r_time between '2019-01-00' and '2019-02-00' or r_time between '2019-02-00' and '2019-03-00' or r_time between '2019-03-00' and '2019-04-00' or r_time between '2019-04-00' and '2019-05-00' or r_time between '2019-05-00' and '2019-06-00' or r_time between '2019-06-00' and '2019-07-00' or r_time between '2019-07-00' and '2019-08-00' or r_time between '2019-08-00' and '2019-09-00' or r_time between '2019-09-00' and '2019-10-00' or r_time between '2019-10-00' and '2019-11-00' or r_time between '2019-11-00' and '2019-12-00' or r_time between '2019-12-00' and '2020-01-00' GROUP BY month ;
2.6 尝试精简 where locate (查找的字符,字段名) 利用
select left(r_time,7) as month,sum(collect) as month_all from oa_money_record where locate ( '2019-03',r_time );
2.7 精简集合
select left(r_time,7) as month,sum(collect) as month_all from oa_money_record where
locate ('2019-01',r_time) or
locate ('2019-02',r_time) or
locate ('2019-03',r_time) or
locate ('2019-04',r_time) or
locate ('2019-05',r_time) or
locate ('2019-06',r_time) or
locate ('2019-07',r_time) or
locate ('2019-08',r_time) or
locate ('2019-09',r_time) or
locate ('2019-10',r_time) or
locate ('2019-11',r_time) or
locate ('2019-12',r_time) GROUP BY month ;
[最终语句]
select left(r_time,7) as month,sum(collect) as month_all from oa_money_record where locate ('2019-01',r_time) or locate ('2019-02',r_time) or locate ('2019-03',r_time) or locate ('2019-04',r_time) or locate ('2019-05',r_time) or locate ('2019-06',r_time) or locate ('2019-07',r_time ) or locate ('2019-08',r_time) or locate ('2019-09',r_time) or locate ('2019-10',r_time) or locate ('2019-11',r_time) or locate ('2019-12',r_time) GROUP BY month ;
总结: GROUP BY 分组字段; 这个分组字段 可以是原始字段 也可以是原始字段经过出来出来的 as[别名] 如 上述操作中, 我的month 是r_time 处理出来的值
|
|