Tính toán giá trị trên cây dữ liệu với sql query

1. Cây dữ liệu có dạng.

TK 111
|_ TK 1111
|_ TK 1112
|_ TK 11121
|_ TK 11122

Giá trị cộng dồn tính như sau lên node cha:

TK 111 (13)
|_ TK 1111 (7)
|_ TK 1112 (6)
|_ TK 11121 (4)
|_ TK 11122 (2)

Tài khoản 1112 có giá trị là 6 vì 2 tài khoản con có tổng giá trị là 6.
Tài khoản 111 có giá trị là 13 vì 2 tài khoản con (1111,1112) có tổng giá trị là 3.
Đó là kết quả mong muốn.

2. Phương pháp làm trong SQL Server.

2.1 Tạo cây dữ liệu

Tạo cây dữ liệu bằng phương pháp đệ quy

;with CayTaiKhoan

as(

select SoHieu,TenTaiKhoan, SoHieu as TKCON

from ChartOfAccounts as a

union all

select b.SoHieu,b.TenTaiKhoan, a.SoHieu as TKCON 

from ChartOfAccounts as a

inner join CayTaiKhoan as b

on b.TKCON = a.TaiKhoanCha -- chu y gan con vao cha

)

2.2 Nối bảng dữ liệu với bảng dữ liệu từng node

;with CayTaiKhoan
as(
select SoHieu,TenTaiKhoan, SoHieu as TKCON
from ChartOfAccounts as a
union all

select b.SoHieu,b.TenTaiKhoan, a.SoHieu as TKCON
from ChartOfAccounts as a
inner join CayTaiKhoan as b
on b.TKCON = a.TaiKhoanCha -- chu y gan con vao cha

)
select *
from  CayTaiKhoan as a
left join @SoPhatSinh as b
on a.TKCON = b.TaiKhoan
order by a.SoHieu

2.3 Tính toán giá trị cho từng node trên cây dữ liệu.

TH1: a.SoHieu = b.TaiKhoan Giá trị riêng của từng node
TH2: a.SoHieu <> b.TaiKhoan Giá trị của các node con

3. Code ví dụ và cách giải quyết.

declare @tungay datetime, @denngay datetime
set dateformat dmy

set @tungay = '01/01/2016'
set @denngay = '31/01/2016'

DECLARE @SoPhatSinh TABLE(
TaiKhoan nvarchar(20),
SoTienNoDauKy float,
SoTienCoDauKy float,
SoTienNoPhatSinh float,
SoTienCoPhatSinh float,
SoTienNoCuoiKy float,
SoTienCoCuoiKy float);


-- du lieu so phat sinh
Insert into @SoPhatSinh
select TaiKhoan,
SUM(SoTienNoDauKy) as SoTienNoDauKy,
SUM(SoTienCoDauKy) as SoTienCoDauKy,
SUM(SoTienNoPhatSinh) as SoTienNoPhatSinh,
SUM(SoTienCoPhatSinh)  as SoTienCoPhatSinh,
case when SUM(SoTienNoDauKy) + SUM(SoTienNoPhatSinh) - SUM(SoTienCoDauKy) - SUM(SoTienCoPhatSinh) > 0
then SUM(SoTienNoDauKy) + SUM(SoTienNoPhatSinh) - SUM(SoTienCoDauKy) - SUM(SoTienCoPhatSinh)
else 0 end as SoTienNoCuoiKy,
case when SUM(SoTienCoDauKy) + SUM(SoTienCoPhatSinh) - SUM(SoTienNoDauKy) - SUM(SoTienNoPhatSinh) > 0
then SUM(SoTienCoDauKy) + SUM(SoTienCoPhatSinh) - SUM(SoTienNoDauKy) - SUM(SoTienNoPhatSinh)
else 0 end as SoTienCoCuoiKy
from
( -- du dau no
select a.TaiKhoan_No as TaiKhoan,
isnull(sum(a.SoTienPhatSinh),0) as SoTienNoDauKy,
0 as SoTienCoDauKy,
0 as SoTienNoPhatSinh,
0 as SoTienCoPhatSinh
from  JournalEntry as a
where a.ChungTuNgayThang < @tungay
group by a.TaiKhoan_No
-- du dau co
union all
select a.TaiKhoan_Co as TaiKhoan,
0 as SoTienNoDauKy,
isnull(sum(a.SoTienPhatSinh),0) as SoTienCoDauKy,
0 as SoTienNoPhatSinh,
0 as SoTienCoPhatSinh
from  JournalEntry as a
where a.ChungTuNgayThang < @tungay
group by a.TaiKhoan_Co
-- phat sinh no
union all
select a.TaiKhoan_No as TaiKhoan,
0 as SoTienNoDauKy,
0 as SoTienCoDauKy,
isnull(sum(a.SoTienPhatSinh),0) as SoTienNoPhatSinh,
0 as SoTienCoPhatSinh
from  JournalEntry as a
where a.ChungTuNgayThang between @tungay and @denngay
group by a.TaiKhoan_No
-- phat sinh co
union all
select a.TaiKhoan_Co as TaiKhoan,
0 as SoTienNoDauKy,
0 as SoTienCoDauKy,
0 as SoTienNoPhatSinh,
isnull(sum(a.SoTienPhatSinh),0) as SoTienCoPhatSinh
from  JournalEntry as a
where a.ChungTuNgayThang between @tungay and @denngay
group by a.TaiKhoan_Co
) as tblmaster
group by TaiKhoan

---- lay du lieu cay tai khoan va tinh luon cho tai khoan cha
;with CayTaiKhoan
as(
select SoHieu,TenTaiKhoan, SoHieu as TKCON
from ChartOfAccounts as a
union all

select b.SoHieu,b.TenTaiKhoan, a.SoHieu as TKCON
from ChartOfAccounts as a
inner join CayTaiKhoan as b
on b.TKCON = a.TaiKhoanCha -- chu y gan con vao cha

)
select a.SoHieu,
a.TenTaiKhoan,
isnull(sum(b.SoTienNoDauKy),0) as SoTienNoDauKy, --không phân biệt giá trị của con hay cha
isnull(sum(b.SoTienCoDauKy),0) as SoTienCoDauKy,
isnull(sum(b.SoTienNoPhatSinh),0) as SoTienNoPhatSinh,
isnull(sum(b.SoTienCoPhatSinh),0) as SoTienCoPhatSinh,
isnull(sum(b.SoTienNoCuoiKy),0) as SoTienNoCuoiKy,
isnull(sum(b.SoTienCoCuoiKy),0) as SoTienCoCuoiKy
from  CayTaiKhoan as a
left join @SoPhatSinh as b
on a.TKCON = b.TaiKhoan
group by a.SoHieu,a.TenTaiKhoan
order by a.SoHieu

Lưu ý: code này không bao gồm trường hợp tài khoản số dư 2 bên, Code chỉ mang tính chất minh họa, khi sử dụng nên cân nhắc.

4. Kết luận

- Cách tính giá trị Node trên cây trong SQL sử dụng đệ quy để tạo một bảng dữ liệu theo chiều Top-Down.
- Kết Bảng dữ liệu vừa tìm được với bảng chứa giá trị tính toán bằng khóa con.
- Thực hiện tính toán giá trị bằng hàm sum và so giá trị của khóa node cha và node con nếu cần.