SQL query code _ MySQL

Chen Youzhang's column (original, which is now the most complex SQL query code) implements the SQL query code for grouping statistics by Department's monthly card balance (which was debugged in MsSQLServer) SELECTdp. dpname1AS department, cust_dp_SumOddfre.sum_oddfareAS total monthly card balance FROM (SELECTT_Department.DpCode1, SUM (custid_SumOd Chen Youzhang's column

(Original, up to now the most complex SQL query code) SQL query code for grouping statistics by Department monthly card balance (debug in Ms SQL Server)

SELECT dp. dpname1 AS department, cust_dp_SumOddfre.sum_oddfare AS total monthly card balance
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 <= '2014-6-1 ')/* enter the month to be queried, and pass the parameters */
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

Appendix: Script for creating basic tables used for query:

Create table [dbo]. [T_CashRec] (-- Cashier's ledger
[StatID] [tinyint] not null,
[CashID] [smallint] not null,
[Port] [tinyint] not null,
[Term] [tinyint] not null,
[CashDt] [datetime] not null, -- deposit and withdrawal time
[CollectDt] [datetime] not null,
[CustomerID] [int] not null,
[OpCount] [int] not null, -- The number of operations performed on a card, only accumulating
[InFare] [money] not null,
[OutFare] [money] not null,
[SumFare] [money] not null,
[OddFare] [money] not null, -- the balance of the card after this operation
[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] (-- consumption Ledger
[StatID] [tinyint] not null,
[Port] [tinyint] not null,
[Term] [tinyint] not null,
[CustomerID] [int] not null,
[OpCount] [int] not null, -- The number of operations performed on a card, only accumulating
[OpDt] [datetime] not null, -- consumption time
[CollectDt] [datetime] not null,
[MealID] [tinyint] not null,
[SumFare] [smallmoney] not null,
[OddFare] [smallmoney] not null, -- the balance of the card after this operation
[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] (-- customer ledger
[CustomerID] [int] not null, -- Customer number, primary key
[StatCode] [varchar] (3) COLLATE Chinese_PRC_CI_AS not null,
[Account] [varchar] (7) COLLATE Chinese_PRC_CI_AS not null, -- unit code
[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] (-- Unit ledger, three-level unit system, tree structure
[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