CTE

使用CTE遞迴實作階層式報表

陳淑敏 2021/12/22 18:33:07
2606

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

 

陳淑敏
相關文章
Ora2pg組態設定(下)
Ora2pg組態設定(上)
SQL Server 2019 資料庫鏡像設定實戰
Neo4j 因果叢集實作簡介
使用CTE遞迴實作階層式報表
SQL Server Function Table 應用
最新文章
網頁動畫製作工具介紹
5步驟教你自動產出WORD文章目錄及圖表目錄
Unit testing LLM models - Lessons from Vertex AI
IntelliJ IDEA 配置 Maven 內網開發環境
Mermaid 語法匯出圖檔格式操作說明 - Mermaid Live Editor, Notion, Draw IO
Project IDX 線上IDE開發程式