mysql余额统计_实现按部门月卡余额总额分组统计的SQL查_mysql

陈优章的专栏

(原创，到现在为至最为复杂的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)

http://www.gaodaima.com/38702.html实现按部门月卡余额总额分组统计的SQL查_mysql

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 <= '20