Mysql 生成统计报表的时候插入汇总行

最新推荐文章于 2024-09-02 17:10:03 发布

weixin_34308389

最新推荐文章于 2024-09-02 17:10:03 发布

阅读量584

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3556610/blog/3056472

版权

2019独角兽企业重金招聘Python工程师标准>>>

   -- 插入汇总行
   INSERT INTO package_aging (
               report_id,
logistics_company_id,
warehouse,
date_applied,
user_id,
user_name,
hour_00,
hour_01,
hour_02,
hour_03,
hour_04,
hour_05,
hour_06,
hour_07,
hour_08,
hour_09,
hour_10,
hour_11,
hour_12,
hour_13,
hour_14,
hour_15,
hour_16,
hour_17,
hour_18,
hour_19,
hour_20,
hour_21,
hour_22,
hour_23,
order_cnt,
pkg_qty,
buy_qty,
sku_qty,
weight,
volume,
created_date,
created_user)
           SELECT k.* FROM
           ( SELECT report_id_ AS report_id ,
           company_id_ AS logistics_company_id,
           warehouse_ AS warehouse ,
           date_applied_ AS date_applied ,
           '*' AS user_id,
           '汇总' AS user_name,
           m.* ,
           calc_date_ AS created_date,
user_id_ as created_user
           FROM (
                   select
       sum(hour_00) as hour_00 ,
       sum(hour_01) as hour_01 ,
       sum(hour_02) as hour_02 ,
       sum(hour_03) as hour_03 ,
       sum(hour_04) as hour_04 ,
       sum(hour_05) as hour_05 ,
       sum(hour_06) as hour_06 ,
       sum(hour_07) as hour_07 ,
       sum(hour_08) as hour_08 ,
       sum(hour_09) as hour_09 ,
       sum(hour_10) as hour_10 ,
       sum(hour_11) as hour_11 ,
       sum(hour_12) as hour_12 ,
       sum(hour_13) as hour_13 ,
       sum(hour_14) as hour_14 ,
       sum(hour_15) as hour_15 ,
       sum(hour_16) as hour_16 ,
       sum(hour_17) as hour_17 ,
       sum(hour_18) as hour_18 ,
       sum(hour_19) as hour_19 ,
       sum(hour_20) as hour_20 ,
       sum(hour_21) as hour_21 ,
       sum(hour_22) as hour_22 ,
       sum(hour_23) as hour_23 ,
       sum(order_cnt) as order_cnt,
       sum(pkg_qty) as pkg_qty ,
       sum(buy_qty) as buy_qty ,
       sum(sku_qty) as sku_qty ,
       sum(weight) as weight ,
       sum(volume) as volume
                               FROM package_aging WHERE report_id = report_id_ AND logistics_company_id = company_id_
                           GROUP BY report_id ,logistics_company_id ) m ) k ;