SQL每日一题(20201105)需要将每个客户按月生成汇总报表

SQL每日一题(20220520)
SQL每日一题(20220704)

select a.*,b.金额 from
(select date_format(t.日期, '%Y-%m') as 日期, c.客户抬头, sum(t.金额) 金额
from t1105c c
         inner join t1105x t on c.客户编号 = t.客户编号
group by date_format(t.日期, '%Y-%m'),c.客户抬头) a
left join
(select date_format(t.日期, '%Y-%m') as 日期, c.客户抬头, sum(t.金额) 金额
from t1105c c
         inner join t1105y t on c.客户编号 = t.客户编号
group by date_format(t.日期, '%Y-%m'),c.客户抬头) b
on a.日期=b.日期 and a.客户抬头=b.客户抬头
order by a.日期

select c.客户抬头
     , a.日期
     , a.X业务金额
     , b.Y业务金额
from (
         select 客户编号
              , substring(日期, 1, 7) 日期
              , sum(金额) as          X业务金额
         from T1105X
         group by 客户编号, substring(日期, 1, 7)
     ) a
         left join (
    select 客户编号
         , substring(日期, 1, 7) 日期
         , sum(金额) as          Y业务金额
    from T1105Y
    group by 客户编号, substring(日期, 1, 7)
) b
                   on a.客户编号 = b.客户编号 and a.日期 = b.日期
         left join T1105C c on a.客户编号 = c.客户编号
order by a.日期, c.客户抬头
;

/*
1.得到t1105y与t1105x left join 后 X表业务的值 union all right join 后Y业务的值 用X.客户编号is null来得到Y业务 不是相同的部分
2.最后在与t1105进行 left join 后排序
*/

 
select a.r, b.客户抬头, a.x, a.y
from (select a.r, a.客户编号, a.x, b.y
      FROM (select DATE_FORMAT(日期, "%Y-%m") as r, 客户编号, SUM(金额) as x from t1105x GROUP BY r, 客户编号) a
               left join (select DATE_FORMAT(日期, "%Y-%m") as r, 客户编号, SUM(金额) as y
                          from t1105y
                          GROUP BY r, 客户编号) b ON a.客户编号 = b.客户编号 and a.r = b.r
      union all
      select b.r, b.客户编号, a.x, b.y
      FROM (select DATE_FORMAT(日期, "%Y-%m") as r, 客户编号, SUM(金额) as x from t1105x GROUP BY r, 客户编号) a
               RIGHT join (select DATE_FORMAT(日期, "%Y-%m") as r, 客户编号, SUM(金额) as y from t1105y GROUP BY r, 客户编号) b
                          ON a.客户编号 = b.客户编号 and a.r = b.r
      WHERE a.客户编号 is NULL) a
         left join t1105c b on a.客户编号 = b.客户编号
ORDER BY r, 客户抬头

/*
如果说上一总写法是X与Y进行 left 或者 right join 得到的话 下面这种方法更加的简便
1.直接对X表求和,给Y业务金额 一个null字段值 对Y表求和 给X金额一个null字段值 然后union 得到X,Y业务金额的和
2.left join T1105A 得到结果
*/

select 日期, D.客户抬头, sum(D.X业务金额) as X业务金额, sum(D.Y业务金额) AS Y业务金额
from (SELECT A.客户抬头, substring(cast(B.日期 as char ), 1, 7) 日期, B.金额 X业务金额, null as Y业务金额
      from T1105C A
               join T1105X B on A.客户编号 = B.客户编号
      union
      SELECT A.客户抬头, substring(cast(C.日期 as char), 1, 7) 日期, null as Y业务金额, C.金额 Y业务金额
      from T1105C A
               join T1105Y C on A.客户编号 = C.客户编号) D
group by D.客户抬头, 日期 order by 日期,D.客户抬头;