Question

目前,我写了:

SELECT IT_ID, SUBSTR (SYS_CONNECT_BY_PATH (grp ,  , ), 2) GROUPS
FROM (SELECT U.IT_ID, LAST_NAME, FIRST_NAME, GRP, ROW_NUMBER() OVER (ORDER BY U.IT_ID) rn, COUNT(*) OVER() cnt
FROM ECG_IT_USERS U
JOIN SECUREGROUPS G ON U.IT_ID = G.IT_ID)

START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1

这一回报:

IT_ID   GROUPS

afz23   ADMIN
afz23   ADMIN,QA
alv77   ADMIN,QA,USER
jaj23   ADMIN,QA,USER,USER
klo26   ADMIN,QA,USER,USER,PROD
klo26   ADMIN,QA,USER,USER,PROD,ADMIN
klo26   ADMIN,QA,USER,USER,PROD,ADMIN,QA
mav45   ADMIN,QA,USER,USER,PROD,ADMIN,QA,ADMIN

我可以说明我如何在遇到新用户后重新启用? 看来,尽管用户不属于前几组人,但该组仍继续工作。

I need to see:

IT_ID   GROUPS

afz23   ADMIN,QA
alv77   USER
jaj23   USER
klo26   PROD,ADMIN,QA
mav45   ADMIN

Answer 1

这里需要做三件事。

首先,你们需要增加分行——编号功能,以便从每个信息技术信息数据库的1个开始。你们还需要增加信息技术。 ID栏与链接,以便它只接收与同一条<代码>IT_ID值的浏览量。最后,您需要按背栏分类,以删除重复浏览。

最后询问

with ECG_IT_USERS as (
  select  afz23  as it_id from dual union all
  select  alv77  as it_id from dual union all
  select  jaj23  as it_id from dual union all
  select  klo26  as it_id from dual union all
  select  mav45  as it_id from dual
),
securegroups as (
  select  afz23  as it_id,  ADMIN  as grp from dual union all
  select  afz23  as it_id,  QA  as grp from dual union all
  select  alv77  as it_id,  USER  as grp from dual union all
  select  jaj23  as it_id,  USER  as grp from dual union all
  select  klo26  as it_id,  PROD  as grp from dual union all
  select  klo26  as it_id,  ADMIN  as grp from dual union all
  select  klo26  as it_id,  QA  as grp from dual union all
  select  mav45  as it_id,  ADMIN  as grp from dual
)
SELECT 
  IT_ID, 
  Max(SUBSTR (SYS_CONNECT_BY_PATH (grp ,  , ), 2)) GROUPS
FROM (
  SELECT 
    U.IT_ID, 
--    LAST_NAME, 
--    BFIRST_NAME, 
    GRP, 
    ROW_NUMBER() OVER (partition by u.it_id ORDER BY U.IT_ID) rn, 
    COUNT(*) OVER() cnt
FROM ECG_IT_USERS U
JOIN SECUREGROUPS G ON (U.IT_ID = G.IT_ID))
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1 and it_id = prior it_id
Group by it_id

这对我产生了以下产出:

IT_ID GROUPS
----- --------------------
alv77 USER
afz23 ADMIN,QA
jaj23 USER
mav45 ADMIN
klo26 PROD,ADMIN,QA

EDIT: I have added a with clause with some sample data and this runs for me without any problems although I did comment out the last_name and first_name columns since they don t have an effect on the final query and I put some brackets around the join condition.

也许要先从上面的问询一开始,检查一下它最初为你工作,并酌情加以修改。

友情链接