陳優(yōu)章的專欄

(原創(chuàng)，到現(xiàn)在為至最為復雜的sql查詢代碼）實現(xiàn)按部門月卡余額總額分組統(tǒng)計的sql查詢代碼（在ms sql server中調(diào)試通過)

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')/*輸入查詢月份,可用參數(shù)傳遞*/
                        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 ,--某卡的操作次數(shù)，只累加
 [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 , --某卡的操作次數(shù)，只累加
 [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] ( --單位帳本,三級單位制,樹型結(jié)構(gòu)
 [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