陳優章的專欄
(原創，到現在為至最為復雜的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 ,
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 ,
