我有一個包含超過 10 萬個郵箱和具有權限的用戶的表。
--------- ---------
| Mailbox | Trustee |
--------- ---------
| smb1 | mbx1 |
| smb2 | mbx1 |
| smb2 | mbx2 |
| smb2 | mbx3 |
| smb3 | mbx4 |
| smb3 | mbx5 |
| mbx1 | mbx6 |
| mbx7 | mbx4 |
| smb4 | mbx8 |
| smb4 | mbx9 |
| mbx8 | mbx10 |
--------- ---------
需要在郵箱列中對受托人和他們有權訪問的郵箱進行分組。例如，mbx1、mbx2 和 mbx 3 通過訪問 smb2 相關聯，因此它們進入存盤桶 1。mbx 進入存盤桶 1 意味著 smb1 也進入存盤桶 1，因為 mbx 1 是該存盤桶的受托人。然后再往下，因為 mbx6 與 mbx1 有關系，它也進入存盤桶 1。希望其他人有意義。請注意，受托人可以訪問 so smb（共享郵箱）或 mbx（郵箱）
我從中選擇的表只有郵箱和受托人，我想寫入下面的臨時表。
--------- --------- --------
| Mailbox | Trustee | Bucket |
--------- --------- --------
| smb1 | mbx1 | 1 |
| smb2 | mbx1 | 1 |
| smb2 | mbx2 | 1 |
| smb2 | mbx3 | 1 |
| smb3 | mbx4 | 2 |
| smb3 | mbx5 | 2 |
| mbx1 | mbx6 | 1 |
| mbx7 | mbx4 | 2 |
| smb4 | mbx8 | 3 |
| smb4 | mbx9 | 3 |
| mbx8 | mbx10 | 3 |
--------- --------- --------
然后，我想將桶數放在一起以形成均勻的組。想法是我可以說例如最大計數 100，因此創建最多可容納約 100 個用戶的存盤桶組。
--------- --------- -------
| Groups | Buckets | Count |
--------- --------- -------
| 1 | 1 | 5 |
| 2 | 2,3 | 6 |
--------- --------- -------
編輯：我已經走到了這一步，我可以通過郵箱獲取所有受托人，然后是受托人可以訪問的其他郵箱。
DECLARE @int int = 1;
WITH Buckets_CTE
(Trustee)
AS (
SELECT DISTINCT Trustee
FROM EXOPerms
WHERE Mailbox = 'smb1'
)
SELECT DISTINCT Mailbox,Trustee
FROM EXOPerms
Where Trustee IN (
SELECT DISTINCT Trustee
FROM Buckets_CTE)
ORDER BY Trustee
The DECLARE Int at the top is redundant at the moment just got that there to see if I can implement the bucket feature.
uj5u.com熱心網友回復：
這是一個while回圈解決方案。它只是遍歷每一行并更新 Bucket。
ID添加用于逐行回圈資料
要檢查郵箱/受托人是否存在于另一行中，請檢查i.Mailbox in (m.Mailbox, m.Trustee)：
from @mailbox i
inner join @mailbox m
on i.ID <> m.ID -- don't compare the same row
and (
i.Mailbox in (m.Mailbox, m.Trustee)
or i.Trustee in (m.Mailbox, m.Trustee)
)
請注意，更新 Bucket 時，它與當前 Bucket 進行比較，并且只取較低的值。這是為了解決像下面這樣的情況，其中早期行之間的關系直到后面的行才知道。
ID MailBox Trustee
1 a b
2 c d
3 e f
4 c f
ID 1, 2, 3 在順序處理時分配單獨的 Bucket。只有當行程 ID 4 時，它將 ID 2 和 3 鏈接在一起
完成查詢
declare @mailbox table
(
ID int identity,
Mailbox varchar(5),
Trustee varchar(5),
Bucket int
)
insert into @mailbox (Mailbox, Trustee) values
( 'smb1', 'mbx1' ),
( 'smb2', 'mbx1' ),
( 'smb2', 'mbx2' ),
( 'smb2', 'mbx3' ),
( 'smb3', 'mbx4' ),
( 'smb3', 'mbx5' ),
( 'mbx1', 'mbx6' ),
( 'mbx7', 'mbx4' ),
( 'smb4', 'mbx8' ),
( 'smb4', 'mbx9' ),
( 'mbx8', 'mbx10');
declare @ID int,
@Bucket int = 1 -- start from 1
-- get the minimum ID for start
select @ID = min(ID) from @mailbox where Bucket is null
while exists
(
select *
from @mailbox
where ID >= @ID
)
begin
-- if the mailbox is found in other row with Bucket value
-- (Bucket is not null)
if exists
(
select *
from @mailbox i
inner join @mailbox m
on i.ID <> m.ID
and (
i.Mailbox in (m.Mailbox, m.Trustee)
or i.Trustee in (m.Mailbox, m.Trustee)
)
where i.ID = @ID
and m.Bucket is not null
)
begin
-- Update Bucket from other row
update i
set Bucket = case when i.Bucket is null
or i.Bucket > m.Bucket
then m.Bucket
else i.Bucket
end
from @mailbox i
inner join @mailbox m
on i.ID <> m.ID
and (
i.Mailbox in (m.Mailbox, m.Trustee)
or i.Trustee in (m.Mailbox, m.Trustee)
)
where i.ID = @ID
and m.Bucket is not null
-- Update other rows that might linked to current ID
update m
set Bucket = case when i.Bucket > m.Bucket
then m.Bucket
else i.Bucket
end
from @mailbox i
inner join @mailbox m
on i.ID <> m.ID
and (
i.Mailbox in (m.Mailbox, m.Trustee)
or i.Trustee in (m.Mailbox, m.Trustee)
)
where i.ID = @ID
end
else
begin
-- no other row found with same mailbox.
-- Assign Bucket from @Bucket, increment @Bucket
update m
set Bucket = @Bucket
from @mailbox m
where m.ID = @ID;
select @Bucket = @Bucket 1;
end
-- Get next ID
select @ID = min(ID) from @mailbox where ID > @ID;
end
select *
from @mailbox
order by ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/446852.html
標籤：sql sql-server tsql
下一篇：將字串中的逗號分隔值與表匹配