CTE
使用CTE遞迴實作階層式報表
2021/12/22 18:33:07
0
2545
1. 前言
本文介紹如何實作如下圖的階層式報表，範本為會計資產負債月報表格式，是由藍框的項目產品及紅框的會計科目餘額構成的報表格式，
以下就介紹如何設計及實作此報表
2. 開始前準備
實作是建立於以下版本的環境：
l SQL Server 2017
l SQL Server Management Studio (SSMS) v18.6
3. 設計及實作說明
1. 查看上表藍框及紅框(A~C欄)，設計如何整合項目產品及會計科目的資料表結構
2. 使用CTE實現遞迴查詢(A~C欄)階層結構的資料
3. 先將階層結構資料與會計餘額資料整合
4. 再依據金額計算說明(F欄)計算各項目產品的會計餘額(E欄)
4. 建置會計餘額表
1. 先建立會計餘額表-[AccountBalance]，執行以下語法即可：
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE [dbo].[AccountBalance]
CREATE TABLE [dbo].[AccountBalance](
ID varchar(20) NOT NULL,
[Name] nvarchar(300) NOT NULL,
Amount numeric(25, 4) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('111110', '現金', 1545627)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('111120', '零用金', -1948969)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('111130', '銀行存款', -112562375)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('111140', '分類為約當現金之銀行透支', 0)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112220', '借出證券', -7332114)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112229', '借出證券評價調整', 3360868)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112230', '開放式基金及貨幣市場工具', 720794912)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112239', '開放式基金及貨幣市場工具評價調整', 15479230)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112511', '換利合約價值', 3092167)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112512', '遠期利率協定合約價值', -1820658)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112513', '資產交換IRS合約價值', 0)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112514', '債券遠期交易', -1830)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112521', '權利金－利率選擇權', -20682764)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112522', '權利金－換利選擇權', 18113913)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112523', '資產交換選擇權', -11960460)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112524', '債券選擇權－非避險', -1429710)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112610', '原始認列時指定為透過損益按公允價值衡量之金融資產 —流動—股票', -715905)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112620', '原始認列時指定為透過損益按公允價值衡量之金融資產 —流動—債券', 4815505)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112630', '原始認列時指定為透過損益按公允價值衡量之金融資產 —流動—其他', -119485)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('112640', '原始認列時指定為透過損益按公允價值衡量之金融資產
—流動—營業證券—自營', -2965071)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('113110', '以成本衡量之金融資產－流動－股票', 3092167)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('113120', '以成本衡量之金融資產－流動－債券', -1820658)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('113130', '以成本衡量之金融資產－流動－其他', 0)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('113150', '以成本衡量之金融資產－流動－營業證券－自營－其他', -1830)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('114061', '應收借貸款項－客戶以其買進證券為擔保', 4921211)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('114062', '備抵呆帳－應收借貸款項－客戶以其買進證券為擔保', 14638)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('114063', '應收借貸款項－客戶以其持有之有價證券為擔保', 0)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('114064', '備抵呆帳－應收借貸款項－客戶以其持有之有價證券為擔保', -47582)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('119010', '暫付款', -20682764)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('119020', '應收代買證券', 18113913)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('119030', '應收託售證券', -11960460)
INSERT [dbo].[AccountBalance] ([ID], [Name], Amount) VALUES ('119040', '代買證券', -1429710)
2. 撈取已建立完成的會計餘額表- [AccountBalance]
SELECT * FROM [dbo].[AccountBalance]
5. 實作演練
1. 查看上表藍框及紅框(A~C欄)，設計如何整合項目產品及會計科目的資料表結構：
l 依據A~C欄建立階層式結構資料，執行以下語法即可：
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE [dbo].[RPCodeItem]
CREATE TABLE [dbo].[RPCodeItem](
CodeID varchar(20) NOT NULL, --項目代碼/會計項目代碼
CodeName nvarchar(300) NOT NULL, --項目名稱
ParentID varchar(20) NOT NULL, --父層代號
Sort int NOT NULL, --排序
IsItem varchar(1) NOT NULL --是否為項目：是=1，否=0
) ON [PRIMARY]
GO
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('110000', '流動資產', '', 1, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('111100', '現金及約當現金', '110000', 2, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112000', '透過損益按公允價值衡量之金融資產－流動', '110000', 3, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('113100', '以成本衡量之金融資產－流動', '110000', 4, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('114060', '應收證券借貸款項', '110000', 5, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('119000', '其他流動資產', '110000', 6, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112200', '持有供交易之金融資產－流動', '112000', 7, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112500', '衍生工具資產－櫃檯', '112000', 8, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112600', '原始認列時指定為透過損益按公允價值衡量之金融資產— 流動', '112000', 9, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112510', '合約價值', '112500', 10, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112520', '買入選擇權', '112500', 11, '1')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('111110', '現金', '111100', 12, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('111120', '零用金', '111100', 13, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('111130', '銀行存款', '111100', 14, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('111140', '分類為約當現金之銀行透支', '111100', 15, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112220', '借出證券', '112200', 16, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112229', '借出證券評價調整', '112200', 17, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112230', '開放式基金及貨幣市場工具', '112200', 18, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112239', '開放式基金及貨幣市場工具評價調整', '112200', 19, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112511', '換利合約價值', '112510', 20, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112512', '遠期利率協定合約價值', '112510', 21, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112513', '資產交換IRS合約價值', '112510', 22, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112514', '債券遠期交易', '112510', 23, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112521', '權利金－利率選擇權', '112520', 24, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112522', '權利金－換利選擇權', '112520', 25, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112523', '資產交換選擇權', '112520', 26, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112524', '債券選擇權－非避險', '112520', 27, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112610', '原始認列時指定為透過損益按公允價值衡量之金融資產 —流動—股票', '112600', 28, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112620', '原始認列時指定為透過損益按公允價值衡量之金融資產 —流動—債券', '112600', 29, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112630', '原始認列時指定為透過損益按公允價值衡量之金融資產 —流動—其他', '112600', 30, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('112640', '原始認列時指定為透過損益按公允價值衡量之金融資產
—流動—營業證券—自營', '112600', 31, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('113110', '以成本衡量之金融資產－流動－股票', '113100', 32, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('113120', '以成本衡量之金融資產－流動－債券', '113100', 33, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('113130', '以成本衡量之金融資產－流動－其他', '113100', 34, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('113150', '以成本衡量之金融資產－流動－營業證券－自營－其他', '113100', 35, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('114061', '應收借貸款項－客戶以其買進證券為擔保', '114060', 36, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('114062', '備抵呆帳－應收借貸款項－客戶以其買進證券為擔保', '114060', 37, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('114063', '應收借貸款項－客戶以其持有之有價證券為擔保', '114060', 38, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('114064', '備抵呆帳－應收借貸款項－客戶以其持有之有價證券為擔保', '114060', 39, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('119010', '暫付款', '119000', 40, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('119020', '應收代買證券', '119000', 41, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('119030', '應收託售證券', '119000', 42, '0')
INSERT [dbo].[RPCodeItem] (CodeID, CodeName, ParentID, Sort, IsItem) VALUES ('119040', '代買證券', '119000', 43, '0')
l 撈取已建立完成的項目產品會計表- [RPCodeItem]
SELECT * FROM [dbo].[RPCodeItem]
2. 使用CTE實現遞迴查詢(A~C欄)階層結構的資料
l 用CTE語法建置成函式方便後續整合時使用，執行以下語法即可：
USE [Test]
GO
/****** Object: UserDefinedFunction [dbo].[fGetItemProdAcc] Script Date: 2021/9/30 下午 06:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
'範 例：
EX1：項目產品明細報表結構
SELECT CodeID, CodeName, AcctID, AcctName, LevelDsp, LevelSort, IsItem
FROM dbo.fGetItemProdAcc()
ORDER BY LevelSort
EX2：撈取會科項目
select * from dbo.fGetItemProdAcc() where IsItem = '0' order by LevelSort
*/
ALTER FUNCTION [dbo].[fGetItemProdAcc]()
RETURNS @AcctInfo TABLE
(
CodeID VARCHAR(50),
CodeName VARCHAR(100),
AcctID VARCHAR(100),
AcctName VARCHAR(100),
IsItem VARCHAR(1),
ParentID VARCHAR(20),
LevelDsp NVARCHAR(200),
LevelSort NVARCHAR(200),
Dp INT
)
AS
BEGIN
;with AcctInfo as
(
--先查出首先要查詢的第一個節點
select
CAST(CodeID AS NVARCHAR(50)) As CodeID,
CAST(CodeName AS NVARCHAR(100)) As CodeName,
CAST('' AS NVARCHAR(100)) AS AcctID,
CAST('' AS NVARCHAR(100)) AS AcctName,
IsItem, ParentID,
CAST(CodeID AS NVARCHAR(100)) AS LevelDsp, --階層
CAST(CodeID AS NVARCHAR(100)) AS LevelSort, --排序
1 AS Dp --階層深度
from RPCodeItem
where ParentID = ''
union all
--將主表與暫存表Join，並且找出對應關係
select
Case When a.IsItem = '1' Then
CAST(a.CodeID AS NVARCHAR(50))
Else
CAST('' AS NVARCHAR(50))
End As CodeID,
Case When a.IsItem = '1' Then
CAST(a.CodeName AS NVARCHAR(100))
Else
CAST('' AS NVARCHAR(50))
End As CodeName,
Case When a.IsItem = '1' Then
CAST('' AS NVARCHAR(50))
Else
CAST(a.CodeID AS NVARCHAR(100))
End
AS AcctID,
Case When a.IsItem = '1' Then
CAST('' AS NVARCHAR(100))
Else
CAST(a.CodeName AS NVARCHAR(100))
End
AS AcctName,
a.IsItem, a.ParentID,
CAST(b.LevelDsp + ',' + a.CodeID AS NVARCHAR(100)) AS LevelDsp, --階層
Case When a.IsItem = '1' Then CAST(b.LevelSort + ',' +a.CodeID AS NVARCHAR(100))
Else CAST(b.LevelSort + ',' +
Case When a.Sort < 100 Then '0'+CAST(a.Sort AS NVARCHAR(50))
Else CAST(a.Sort AS NVARCHAR(50))
End AS NVARCHAR(100)) End AS LevelSort, --排序
b.Dp + 1 AS Dp --階層深度
from RPCodeItem a
inner join AcctInfo b on a.ParentID = b.CodeID
where a.ParentID != ''
)
INSERT INTO @AcctInfo
select *
from AcctInfo
--------------------------------------------------------------------------
RETURN
--------------------------------------------------------------------------
END;
l 撈取項目產品明細報表結構資料
SELECT CodeID, CodeName, AcctID, AcctName, LevelDsp, LevelSort, IsItem
FROM dbo.fGetItemProdAcc()
ORDER BY LevelSort
3. 先將階層結構資料與會計餘額資料整合
l 呼叫上述作好的函式關聯會計餘額表即完成會計餘額階層結構化報表，執行以下語法即可：
select *
from
(
SELECT CodeID, CodeName, AcctID, AcctName, LevelDsp, LevelSort, IsItem
FROM dbo.fGetItemProdAcc()
) a
left join [dbo].[AccountBalance] b
on a.AcctID = b.ID
ORDER BY a.LevelSort
4. 再依據金額計算說明(F欄)計算各項目產品的會計餘額(E欄)，
l 利用上述函式內的LevelDsp (階層)欄位搜尋項目代號(A欄)即可加總該項目的會計餘額，執行以下語法即可：
--先將階層結構資料與會計餘額資料整合
select *
into #AccResult
from
(
SELECT CodeID, CodeName, AcctID, AcctName, LevelDsp, LevelSort, IsItem
FROM dbo.fGetItemProdAcc()
) a
left join [dbo].[AccountBalance] b
on a.AcctID = b.ID
ORDER BY a.LevelSort
--再依據金額計算說明(F欄)計算各項目產品的會計餘額(E欄)
select a.CodeID, a.CodeName, a.AcctName, ISNULL(a.ID, '') as ID,
ISNULL(a.[Name], '') as [Name], ISNULL(a.Amount, b.Amount) as Amount
from #AccResult a
left join
(
select a.CodeID, sum(Amount) as Amount
from
(
select CodeID, levelDsp from #AccResult
where IsItem = '1'
) a
left join
(
select levelDsp, Amount from #AccResult
where IsItem = '0'
) b
on CHARINDEX(','+a.levelDsp+',', ','+b.levelDsp+',') > 0 --模糊比對兩個table裡的某個欄位，用CHARINDEX語法
group by a.CodeID
) b
on a.CodeID = b.CodeID
order by a.LevelSort
drop table #AccResult
6. 參考來源
l CTE遞迴查詢
https://dotblogs.azurewebsites.net/wasichris/2016/11/03/151251
l 模糊比對兩個table裡的某個欄位
http://blueshop.com.tw/board/FUM20041006152735ZFS/BRD20121024153733Y34.html
相關文章
