(原创，到现在为至最为复杂的SQL查询代码）实现按部门月卡余额总额分组统计的SQL查询代码（在Ms SQL Server中调试通过)[1]

select dp.dpname1 as 部门, cust_dp_sumoddfre.sum_oddfare as 当月卡总余额
from (select t_department.dpcode1, sum(custid_sumoddfare_group.sum_oddfare)
              as sum_oddfare
        from (select l2.customerid, sum(r1.oddfare) as sum_oddfare
                from (select customerid, max(opcount) as max_opcount
                        from (select customerid, opcount, rtrim(cast(year(opdt)
                                      as char)) + '-' + rtrim(cast(month(opdt) as char))
                                      + '-' + rtrim(day(0)) as dt
                                from t_consumerec
                                union
                                select customerid, opcount, rtrim(cast(year(cashdt)
                                      as char)) + '-' + rtrim(cast(month(cashdt) as char))
                                      + '-' + rtrim(day(0)) as dt
                                from t_cashrec) l1
                        where (dt <= '2005-6-1')/*输入查询月份,可用参数传递*/
                        group by customerid) l2 inner join
                          (select customerid, opcount, oddfare
                         from t_consumerec
                         union
                         select customerid, opcount, oddfare
                         from t_cashrec) r1 on l2.customerid = r1.customerid and
                      r1.opcount = l2.max_opcount
                group by l2.customerid) custid_sumoddfare_group inner join
              t_customers on
              custid_sumoddfare_group.customerid = t_customers.customerid inner join
              t_department on substring(t_customers.account, 1, 2)
              = t_department.dpcode1 and substring(t_customers.account, 3, 2)
              = t_department.dpcode2 and substring(t_customers.account, 5, 3)
              = t_department.d