SQL - 获取多机构最近相同节点
时间：2021-07-01 10:21:17
帮助过：3人阅读
Create Branches Table
create table Branches
(
BranchCode varchar(
16)
,BranchName nvarchar(
32)
,L0BCode varchar(
16)
,L1BCode varchar(
16)
,L2BCode varchar(
16)
,L3BCode varchar(
16)
,L4BCode varchar(
16)
,L5BCode varchar(
16)
,L6BCode varchar(
16)
,L7BCode varchar(
16)
)
go
-------------------------------------------------------------------------------------------
declare @branches varchar(
512)
= ‘02078,31696,90100‘
,@count int
,@branchcode varchar(
16)
select @count = COUNT(
*)
from string_split(
@branches,
‘,‘)
;with t1
as
(
select b.L0BCode,b.L1BCode,b.L2BCode,b.L3BCode,b.L4BCode,b.L5BCode,b.L6BCode,b.L7BCode
,iif(isnull(b.L0BCode,
‘‘)
= ‘‘,
@count,dense_rank()
over(
order by b.L0BCode ))
as L0Rank
,iif(isnull(b.L1BCode,
‘‘)
= ‘‘,
@count,dense_rank()
over(
order by b.L1BCode ))
as L1Rank
,iif(isnull(b.L2BCode,
‘‘)
= ‘‘,
@count,dense_rank()
over(
order by b.L2BCode ))
as L2Rank
,iif(isnull(b.L3BCode,
‘‘)
= ‘‘,
@count,dense_rank()
over(
order by b.L3BCode ))
as L3Rank
,iif(isnull(b.L4BCode,
‘‘)
= ‘‘,
@count,dense_rank()
over(
order by b.L4BCode ))
as L4Rank
,iif(isnull(b.L5BCode,
‘‘)
= ‘‘,
@count,dense_rank()
over(
order by b.L5BCode ))
as L5Rank
,iif(isnull(b.L6BCode,
‘‘)
= ‘‘,
@count,dense_rank()
over(
order by b.L6BCode ))
as L6Rank
,iif(isnull(b.L7BCode,
‘‘)
= ‘‘,
@count,dense_rank()
over(
order by b.L7BCode ))
as L7Rank
from
Branches b
inner join
string_split(@branches,
‘,‘) b2
on
b.BranchCode = b2.value
)
,t2
as
(
select
top 1 *
from
(
select
b.L0BCode,b.L1BCode,b.L2BCode,b.L3BCode,b.L4BCode,b.L5BCode,b.L6BCode,b.L7BCode
,sum(L0Rank)
over(partition
by 1)
as L0Sum
,sum(L1Rank)
over(partition
by 1)
as L1Sum
,sum(L2Rank)
over(partition
by 1)
as L2Sum
,sum(L3Rank)
over(partition
by 1)
as L3Sum
,sum(L4Rank)
over(partition
by 1)
as L4Sum
,sum(L5Rank)
over(partition
by 1)
as L5Sum
,sum(L6Rank)
over(partition
by 1)
as L6Sum
,sum(L7Rank)
over(partition
by 1)
as L7Sum
from t1
as b
) as b
order by b.L7BCode
desc,b.L6BCode
desc,b.L5BCode
desc,b.L4BCode
desc,b.L3BCode
desc,b.L2BCode
desc,b.L1BCode
desc
)
select
@branchcode =
(
case
when L7Sum
= @count then L7BCode
when L6Sum
= @count then L6BCode
when L5Sum
= @count then L5BCode
when L4Sum
= @count then L4BCode
when L3Sum
= @count then L3BCode
when L2Sum
= @count then L2BCode
when L1Sum
= @count then L1BCode
when L0Sum
= @count then L0BCode
else
L0BCode
end
)
from t2
select @branchcode
SQL - 获取多机构最近相同节点
标签：bsp from esc code 获取 相同 value varchar ring
