excel - 如何基于字符串连接多个引用表

问题描述

假设给了我三张表来从中提取数据，我需要从这些表中获取重复数据。基本上对于用户组中的每个用户，查找与另一个组关联的所有权限并显示给定的权限。我的床单就是这样设置的。（假设左上角是A1）

第一个表（用户表）

Username    Group
GPARR11     ACC-ADMIN
LPARR11     PRT-MGR
CSMITH      VP-SALES

第二个表（权限表）

Group       Ref Table Rows  Switches
ACC-ADMIN   500             YNYNNNYN
ACC-ADMIN   502             YNYYY
ACC-ADMIN   503             NYNYYN
PRT-MGR     500             NNNYNNNYN
PRT-MGR     633             YNYNNNYNNY
VP-Sales    500             NYNYNNNYNY
VP-Sales    999             NYNYNYNYNYNNYNNYNYNN

决赛桌（参考表）

这张表从第二张表的第三列中得到它的引用。因此，如果一个角色是Y，那么它启用了该权限，N那么它没有。

Ref Table Switch Row    Sequenc #   Permission
500                        1        Access G&B
500                        2        Access Call
500                        3        Access A/R
633                        1        Modify G&B
633                        2        Modify Call
633                        3        Modify Memos
999                        1        Delete G&B

我期待看到的

Username    Group       Permission    Enabled
GPARR11     ACC-ADMIN   Access G&B    TRUE
                        Access Call   FALSE
                        Access A/R    TRUE

LPARR11     PRT-MGR     Access G&B    FALSE
                        Access Call   FALSE
                        Access A/R    FALSE
                        Modify G&B    TRUE
                        Modify Call   FALSE
                        Modify Memos  TRUE

CSMITH38    VP-SALES    Access G&B    FALSE
                        Access Call   TRUE
                        Access A/R    FALSE

添加信息

本质上，信息应该流以获取每个预期输出的方式如下。对于每个用户名，将他们的组名与第二张表中的组名匹配。从那里Ref Table Rows将告诉您允许返回哪些行，将Switches (YNYNYN)与参考表中的相应 Ref Table Rows 映射。序列号用于确定哪个权限与相应的 Switch 匹配。1 是第一个字符，2 是第二个字符，依此类推。并确定它在输出上返回 true 还是 false。

查看权限表中GPARR11的ACC-ADMIN匹配项。ACC-ADMIN有三个Ref Table Rows与ACC-ADMIN, 500, 502,相关联503。每行都有Switches对应的Reference Table. 因此它将从500, 502,中提取所有权限503，然后检查switch以确定每个权限是否ref rows是TRUE或FALSE

有没有办法用公式来做到这一点？

标签： excelexcel-formuladb2

解决方案

试试这个：

WITH 
  TAB1 (Username, Group) AS
(VALUES
  ('GPARR11', 'ACC-ADMIN')
, ('LPARR11', 'PRT-MGR  ')
, ('CSMITH ', 'VP-SALES ')
)
, TAB2 (Group, Ref, Switches) AS
(
VALUES
  ('ACC-ADMIN', 500, 'YNYNNNYN')
, ('ACC-ADMIN', 502, 'YNYYY')
, ('ACC-ADMIN', 503, 'NYNYYN')
, ('PRT-MGR  ', 500, 'NNNYNNNYN')
, ('PRT-MGR  ', 633, 'YNYNNNYNNY')
, ('VP-SALES ', 500, 'NYNYNNNYNY')
, ('VP-SALES ', 999, 'NYNYNYNYNYNNYNNYNYNN')
)
, TAB3 (Ref, Sequence, Permission) AS
(
VALUES
  (500, 1, 'Access G&B  ')
, (500, 2, 'Access Call ')
, (500, 3, 'Access A/R  ')
, (633, 1, 'Modify G&B  ')
, (633, 2, 'Modify Call ')
, (633, 3, 'Modify Memos')
, (999, 1, 'Delete G&B  ')
)
SELECT 
  CASE WHEN ROWNUMBER() OVER (PARTITION BY T1.Username, T2.Group ORDER BY T3.Sequence) = 1 THEN T1.Username ELSE '' END Username
, CASE WHEN ROWNUMBER() OVER (PARTITION BY T1.Username, T2.Group ORDER BY T3.Sequence) = 1 THEN T2.Group    ELSE '' END Group
, T3.Permission, DECODE(T.Flag, 'Y', 'TRUE', 'FALSE') Enabled
FROM TAB1 T1
JOIN TAB2 T2 ON T2.Group = T1.Group
CROSS JOIN xmltable
(
'let $n := substring(replace($s, "(.)", " $1"), 2) for $id in tokenize($n, " ") return <i>{string($id)}</i>' 
passing 
  t2.switches as "s"
columns 
  seq for ordinality
, flag char(1) path '.'
) T
JOIN TAB3 T3 ON T3.Ref = T2.Ref AND T3.Sequence = T.Seq 
ORDER BY T1.Username, T2.Group, T3.Sequence;

结果是：

|USERNAME|GROUP    |PERMISSION  |ENABLED|
|--------|---------|------------|-------|
|CSMITH  |VP-SALES |Access G&B  |FALSE  |
|        |         |Delete G&B  |FALSE  |
|        |         |Access Call |TRUE   |
|        |         |Access A/R  |FALSE  |
|GPARR11 |ACC-ADMIN|Access G&B  |TRUE   |
|        |         |Access Call |FALSE  |
|        |         |Access A/R  |TRUE   |
|LPARR11 |PRT-MGR  |Access G&B  |FALSE  |
|        |         |Modify G&B  |TRUE   |
|        |         |Access Call |FALSE  |
|        |         |Modify Call |FALSE  |
|        |         |Access A/R  |FALSE  |
|        |         |Modify Memos|TRUE   |

dbfiddle 链接。