实现按部门月卡余额总额分组统计的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
       &nb