实现按部门月卡余额总额分组统计的SQL查询代码

陈优章的专栏

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

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.dpcode3
        group by dpcode1) cust_dp_sumoddfre inner join
          (select distinct dpcode1, dpname1
         from t_department) dp on dp.dpcode1 = cust_dp_sumoddfre.dpcode1

附：查询用到的基本表形成脚本：

create table [dbo].[t_cashrec] ( --出纳明细账本
 [statid] [tinyint] not null ,
 [cashid] [smallint] not null ,
 [port] [tinyint] not null ,
 [term] [tinyint] not null ,
 [cashdt] [datetime] not null ,--存取款时间
 [collectdt] [datetime] not null ,
 [customerid] [int] not null ,
 [opcount] [int] not null ,--某卡的操作次数，只累加
 [infare] [money] not null ,
 [outfare] [money] not null ,
 [sumfare] [money] not null ,
 [oddfare] [money] not null ,--此次操作后该卡的余额
 [mngfare] [money] not null ,
 [hz] [tinyint] not null ,
 [cursum] [smallmoney] null ,
 [curcount] [smallint] null ,
 [cardsn] [tinyint] null
) on [primary]
go

create table [dbo].[t_consumerec] ( --消费明细账本
 [statid] [tinyint] not null ,
 [port] [tinyint] not null ,
 [term] [tinyint] not null ,
 [customerid] [int] not null ,
 [opcount] [int] not null , --某卡的操作次数，只累加
 [opdt] [datetime] not null ,--消费时间
 [collectdt] [datetime] not null ,
 [mealid] [tinyint] not null ,
 [sumfare] [smallmoney] not null ,
 [oddfare] [smallmoney] not null ,--此次操作后该卡的余额
 [mngfare] [smallmoney] not null ,
 [opfare] [smallmoney] not null ,
 [hz] [tinyint] not null ,
 [menuid] [smallint] null ,
 [menunum] [tinyint] null ,
 [oddfarepre] [smallmoney] null ,
 [recno] [smallint] null ,
 [cardsn] [tinyint] not null ,
 [cardver] [tinyint] null
) on [primary]
go

create table [dbo].[t_customers] ( --客户账本
 [customerid] [int] not null , --客户号，主键
 [statcode] [varchar] (3) collate chinese_prc_ci_as not null ,
 [account] [varchar] (7) collate chinese_prc_ci_as not null ,--单位代号
 [name] [varchar] (12) collate chinese_prc_ci_as not null ,
 [cardno] [int] not null ,
 [cardsn] [tinyint] null ,
 [cardtype] [tinyint] not null ,
 [status] [tinyint] not null ,
 [opendt] [datetime] not null ,
 [cashid] [smallint] not null ,
 [sumfare] [smallmoney] not null ,
 [consumefare] [smallmoney] not null ,
 [oddfare] [smallmoney] not null ,
 [opcount] [int] not null ,
 [cursubsidyfare] [smallmoney] not null ,
 [subsidydt] [datetime] not null ,
 [subsidyout] [char] (1) collate chinese_prc_ci_as not null ,
 [alias] [varchar] (10) collate chinese_prc_ci_as null ,
 [outid] [varchar] (20) collate chinese_prc_ci_as null ,
 [updateid] [tinyint] not null ,
 [pwd] [char] (4) collate chinese_prc_ci_as null ,
 [quchargfare] [smallmoney] null ,
 [hastaken] [tinyint] null ,
 [dragoncardno] [char] (19) collate chinese_prc_ci_as null ,
 [applycharg] [smallmoney] null ,
 [chargper] [smallmoney] null ,
 [mingzu] [varchar] (20) collate chinese_prc_ci_as null ,
 [sex] [char] (2) collate chinese_prc_ci_as null ,
 [memo] [varchar] (100) collate chinese_prc_ci_as null ,
 [weipeidw] [varchar] (10) collate chinese_prc_ci_as null ,
 [cardconsumetype] [tinyint] null ,
 [leaveschooldt] [datetime] null ,
 [usevaliddt] [tinyint] not null ,
 [nousedate] [datetime] not null
) on [primary]
go

create table [dbo].[t_department] ( --单位帐本,三级单位制,树型结构
 [dpcode1] [char] (2) collate chinese_prc_ci_as not null ,
 [dpcode2] [char] (2) collate chinese_prc_ci_as null ,
 [dpcode3] [char] (3) collate chinese_prc_ci_as null ,
 [dpname1] [varchar] (30) collate chinese_prc_ci_as null ,
 [dpname2] [varchar] (30) collate chinese_prc_ci_as null ,
 [dpname3] [varchar] (30) collate chinese_prc_ci_as null ,
 [n_sr] [int] not null ,
 [batnum] [smallint] null
) on [primary]
go