oracle 用于查找交叉连接内唯一匹配项的SQL查询

beq87vna  于 2023-03-22  发布在  Oracle
关注(0)|答案(2)|浏览(149)

我有三张table：用户、权限和用户权限，Map前两个表中的值。示例：
表“用户”
| 识别号|登录|
| - ------|- ------|
| 1个|测试1|
| 第二章|测试2|
表“权限”
| 识别号|姓名|
| - ------|- ------|
| 1个|读出|
| 第二章|写|
| 三个|写|
表“用户权限”
| 用户ID|右侧ID|
| - ------|- ------|
| 1个|1个|
| 1个|第二章|
| 1个|三个|
| 第二章|第二章|
我有一个查询来检查用户是否具有适当的权限。查询的输出如下所示：
| 登录|权利|
| - ------|- ------|
| 测试1|读取=真;写入=真|
| 测试2|return false;write=true|
问题是在表'Rights'中可能存在一些与其他关系重复的内容。因此脚本输出可能如下所示：
| 登录|权利|
| - ------|- ------|
| 测试1|return true; return true;write=true|
| 测试2|return false;write=true;write=false|
我想只选择不同的权利，如果至少有一个副本具有“true”，我想为每个这样的权利选择“true”值。
我使用这个查询来获得预期的结果，但它没有考虑是否有任何重复的右边值为“true”：

SELECT u.login,
       LISTAGG(DISTINCT r.name || '=' || 
               CASE
                 WHEN ur.user_id IS NOT NULL THEN
                  'true'
                 ELSE
                  'false'
               END,
               '; ') 
       WITHIN GROUP ( ORDER BY r.name ) AS rights
  FROM users u
 CROSS JOIN rights r
  LEFT JOIN userrights ur
    ON ur.user_id = u.id
   AND ur.right_id = r.id
 GROUP BY u.login;

另外，我想摆脱打印r.name，但我使用它来过滤出唯一的值。
我们现在正在从这个模式迁移，因此纠正DB一致性比纠正脚本更困难。

oracle

2条答案

按热度按时间
krugob8w

krugob8w1#

通过向表中添加主键/唯一约束来修复数据（而不是查询）：

CREATE TABLE Users (
  ID    NUMBER(8,0) PRIMARY KEY,
  LOGIN VARCHAR2(20) NOT NULL UNIQUE
);

CREATE TABLE Rights (
  ID   NUMBER(5,0) PRIMARY KEY,
  NAME VARCHAR2(20) UNIQUE NOT NULL
);

CREATE TABLE UserRights (
  USER_ID REFERENCES Users(id),
  RIGHT_ID REFERENCES Rights(id),
  PRIMARY KEY (user_id, right_id)
);

INSERT INTO users (id, login)
SELECT 1, 'test1' FROM DUAL UNION ALL
SELECT 2, 'test2' FROM DUAL;

INSERT INTO rights (id, name)
SELECT 1, 'read' FROM DUAL UNION ALL
SELECT 2, 'write' FROM DUAL;

INSERT INTO UserRights (USER_ID, RIGHT_ID)
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL;

那么在一个表中就不可能有重复的权限。
也不可能（具有或不具有约束）具有同时是true和false的权限，因为true由UserRights表中匹配行的存在给出，而false由非匹配行的存在给出。在UserRights表中存在匹配行，并且对于相同的用户和权限组合，在UserRights表中既存在行又不存在行的情况是不可能的。UserRights表;这些行为是相互排斥的。
如果在应用约束之前有重复项，则可以使用以下命令将其删除：

DELETE FROM userrights
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER (PARTITION BY user_id, right_id ORDER BY ROWID) AS rn
    FROM   userrights
  )
  WHERE rn > 1
);

删除重复项并应用约束后，您可以使用查询（或聚合主键）：

SELECT MAX(u.login) AS login,
       LISTAGG(r.name || '=' || NVL2(ur.user_id, 'true', 'false'), ';')
         WITHIN GROUP (ORDER BY r.name) AS rights
FROM   users u
       CROSS JOIN rights r
       LEFT OUTER JOIN userrights ur
       ON (u.id = ur.user_id AND r.id = ur.right_id)
GROUP BY u.id

对于您的示例数据，输出：
| 登录|权利|
| - ------|- ------|
| 测试1|读取=真;写入=假|
| 测试2|读取=真;写入=真|
fiddle
如果在UserRights表中有重复的数据，那么应该修复数据并添加约束;如果您不打算这样做（您应该这样做），则可以使用DISTINCT：

SELECT MAX(u.login) AS login,
       LISTAGG(r.name || '=' || NVL2(ur.user_id, 'true', 'false'), ';')
         WITHIN GROUP (ORDER BY r.name) AS rights
FROM   users u
       CROSS JOIN rights r
       LEFT OUTER JOIN (SELECT DISTINCT * FROM userrights) ur
       ON (u.id = ur.user_id AND r.id = ur.right_id)
GROUP BY u.id;

或：

SELECT MAX(u.login) AS login,
       LISTAGG(DISTINCT r.name || '=' || NVL2(ur.user_id, 'true', 'false'), ';')
         WITHIN GROUP (ORDER BY r.name) AS rights
FROM   users u
       CROSS JOIN rights r
       LEFT OUTER JOIN userrights ur
       ON (u.id = ur.user_id AND r.id = ur.right_id)
GROUP BY u.id;

fiddle

更新：重名权限

如果您有重复名称的权限，请修复数据。
例如，给定数据：

CREATE TABLE Users (
  ID    NUMBER(8,0) PRIMARY KEY,
  LOGIN VARCHAR2(20) NOT NULL UNIQUE
);

CREATE TABLE Rights (
  ID   NUMBER(5,0) PRIMARY KEY,
  NAME VARCHAR2(20) NOT NULL
);

CREATE TABLE UserRights (
  USER_ID REFERENCES Users(id),
  RIGHT_ID REFERENCES Rights(id),
  CONSTRAINT userrights__uid__rid__pk PRIMARY KEY (user_id, right_id)
);

INSERT INTO users (id, login)
SELECT 1, 'test1' FROM DUAL UNION ALL
SELECT 2, 'test2' FROM DUAL UNION ALL
SELECT 3, 'test3' FROM DUAL UNION ALL
SELECT 4, 'test4' FROM DUAL;

INSERT INTO rights (id, name)
SELECT 1, 'read' FROM DUAL UNION ALL
SELECT 2, 'write' FROM DUAL UNION ALL
SELECT 3, 'write' FROM DUAL;

INSERT INTO UserRights (USER_ID, RIGHT_ID)
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 4, 3 FROM DUAL;

然后，您可以使用以下命令合并重复项：

ALTER TABLE userrights MODIFY CONSTRAINT userrights__uid__rid__pk DISABLE;

UPDATE userrights ur
SET right_id = (
  SELECT new_id
  FROM   (
    SELECT id,
           MIN(id) OVER (PARTITION BY name) AS new_id
    FROM   rights
  ) r
  WHERE  r.id = ur.right_id
);

DELETE FROM userrights
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER (PARTITION BY user_id, right_id ORDER BY ROWID) AS rn
    FROM   userrights
  )
  WHERE  rn > 1
)

ALTER TABLE userrights MODIFY CONSTRAINT userrights__uid__rid__pk ENABLE;

并删除（现在未使用的）重复权限：

DELETE FROM rights
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
    FROM   rights
  )
  WHERE  rn > 1
)

然后，您可以添加一个约束来防止这种情况再次发生：

ALTER TABLE rights ADD CONSTRAINT rights__name__u UNIQUE (name);

然后：

SELECT MAX(u.login) AS login,
       LISTAGG(r.name || '=' || NVL2(ur.user_id, 'true', 'false'), ';')
         WITHIN GROUP (ORDER BY r.name) AS rights
FROM   users u
       CROSS JOIN rights r
       LEFT OUTER JOIN userrights ur
       ON (u.id = ur.user_id AND r.id = ur.right_id)
GROUP BY u.id

输出：
| 登录|权利|
| - ------|- ------|
| 测试1|读取=真;写入=假|
| 测试2|读取=真;写入=真|
| 测试3|return false;write=true|
| 测试4|return false;write=true|
fiddle

赞(0）分享  回复(0）举报  2023-03-22
hec6srdp

hec6srdp2#

这样做的一个选项可能是这样的：

SELECT  LOGIN, 
        CASE WHEN READ_RIGHT Is Null THEN 'Read=false;' ELSE 'Read=true;' END || 
        CASE WHEN WRITE_RIGHT Is Null THEN 'Write=false;' ELSE 'Write=true;' END "RIGHTS"
FROM
    (   SELECT  u.LOGIN, ROW_NUMBER() OVER(Partition By u.ID Order  By u.ID, Nvl(r.ID, 0)) "RN",
                CASE  WHEN Upper(r.A_NAME) = 'READ' THEN r.A_NAME 
                ELSE  FIRST_VALUE(CASE  WHEN Upper(r.A_NAME) = 'READ' THEN r.A_NAME END) 
                                  OVER(Partition By u.ID Order  By u.ID, Nvl(r.ID, 0) Rows Between 1 Following And Unbounded Following) END "READ_RIGHT" ,
                --
                CASE  WHEN Upper(r.A_NAME) = 'WRITE' THEN r.A_NAME 
                ELSE FIRST_VALUE(CASE   WHEN Upper(r.A_NAME) = 'WRITE' THEN r.A_NAME END) 
                                 OVER(Partition By u.ID Order  By u.ID, Nvl(r.ID, 0) Rows Between 1 Following And Unbounded Following) END "WRITE_RIGHT"
        FROM    users u
        LEFT JOIN users_rights ur ON(u.ID = ur.USER_ID)
        LEFT JOIN rights r ON(r.ID = ur.RIGHT_ID)
        ORDER BY  u.ID, r.ID  
    )
WHERE   RN = 1

子查询检查任何特定用户是否具有读、写或两者都有权限，并将其放置在用户的第一行。主查询只是连接值，如果它们存在或不存在，则给出true或false。
使用示例数据：

WITH
    users AS
        (   Select 1 "ID",  'test1' "LOGIN" From Dual Union All
            Select 2,       'test2'         From Dual
        ),
    rights AS
        (   Select 1 "ID",  'read'  "A_NAME"    From Dual Union All
            Select 2,       'write'             From Dual Union All
            Select 3,       'write'             From Dual
        ),
    users_rights AS
        (   Select 1 "USER_ID", 1   "RIGHT_ID"  From Dual Union All
            Select 1,           2               From Dual Union All
            Select 1,           3               From Dual Union All
            Select 2,           2               From Dual
        )

...那就是

R e s u l t :
LOGIN RIGHTS                
----- -----------------------
test1 Read=true;Write=true;   
test2 Read=false;Write=true;

另外，如果你有一个用户不在users_rights表中，假设用户ID=3 LOGIN=test3，那么这将返回另一行，如下所示：

LOGIN RIGHTS                
----- -----------------------
test1 Read=true;Write=true;   
test2 Read=false;Write=true;  
test3 Read=false;Write=false;
赞(0）分享  回复(0）举报  2023-03-22
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com