我正在使用 PostgreSQL 13。我有兩個這樣的表：
權限表
| 姓名 | 允許 |
|---|---|
| 安 | 閱讀發票 |
| 安 | 開具發票 |
| 安 | 執行付款 |
| 鮑勃 | 讀取員工資料 |
| 鮑勃 | 修改人員資料 |
| 鮑勃 | 執行工資單 |
| 卡爾 | 閱讀發票 |
| 卡爾 | 開具發票 |
風險表
| risk_id | 允許 |
|---|---|
| 風險1 | 閱讀發票 |
| 風險1 | 開具發票 |
| 風險1 | 執行付款 |
| 風險2 | 讀取員工資料 |
| 風險2 | 修改人員資料 |
| 風險2 | 執行工資單 |
我想創建一個新表，其中包含第一個表的員工姓名，其權限在第二個表中被指出為風險。執行后，結果應該是這樣的：
| 姓名 | risk_id |
|---|---|
| 安 | 風險1 |
| 鮑勃 | 風險2 |
由于 Carl 只有屬于 Risk2 的三個權限中的兩個，因此他不會被包括在結果中。
我的第一個蠻力方法是將屬于風險的權限串列與屬于員工的權限進行比較。如果第一個串列包含在第二個串列中，則員工/風險的組合將添加到結果表中。
INSERT INTO results_table
SELECT a.employee, b.risk_id FROM permission_table a, risk_table b WHERE
((SELECT permission FROM risk_table c WHERE b.permission = c.permission ) EXCEPT
(SELECT permission FROM permission_table d WHERE a.employee=d.employee)
) IS NULL;
我不確定使用這種方法的結果是否正確，因為如果表很大，即使我添加了一個 WHERE 子句將查詢限制為僅限一名員工，也需要很長時間。
能否請你幫忙？
uj5u.com熱心網友回復：
解決這個問題的一種方法是
- 計算每個“ risk_id ”值的權限數量
- 加入“權限”和“風險”表，計數匹配“權限”值
- 確保每個三元組“ <permissions.name, risk.risk_id, risk.cnt> ”的不同權限計數對應于全部權限。
WITH risks_with_counts AS (
SELECT *, COUNT(permission) OVER(PARTITION BY risk_id) AS cnt
FROM risks
)
SELECT p.name, r.risk_id
FROM permissions p
INNER JOIN risks_with_counts r
ON p.permission = r.permission
GROUP BY p.name, r.risk_id, r.cnt
HAVING COUNT(DISTINCT r.permission) = r.cnt
Carl 不會包含在輸出中，因為他沒有來自“ risk_id = 'Risk 1' ”的所有權限
在此處查看演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/517008.html
標籤：PostgreSQLsql 除外
上一篇：如何計算自參考列中出現的次數？