老莫的笔记本  
  
请选择 进入手机版 | 继续访问电脑版
查看: 1045|回复: 0

在财务的表中 得出 一年内 每个月 所得的收入总和

[复制链接]

662

主题

878

帖子

5115

积分

超级版主

Rank: 8Rank: 8

积分
5115
发表于 2019-5-21 16:56:31 | 显示全部楼层 |阅读模式
本帖最后由 周大胖子 于 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 处理出来的值



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表