데이터베이스 질문과 답변
쿼리 질문드립니다 .
0
2
4,177
by yellowplac [2024.04.25 17:57:37]
mysql 쿼리입니다 . select ROWNUM, MAX(case when TBL.CHLDRN_ROLE = 'ROOT' then TBL.PARNTS_ROLE else null end) AS A1, MAX(case when TBL.CHLDRN_ROLE = 'ROLE_ADMIN' then TBL.PARNTS_ROLE else null end) as A2, MAX(case when TBL.CHLDRN_ROLE = 'ROLE_USER_MEMBER' then TBL.PARNTS_ROLE else null end) as A3 from ( select row_number() over(order by CHLDRN_ROLE) as ROWNUM,CHLDRN_ROLE,PARNTS_ROLE from LETTNROLES_HIERARCHY ) as TBL group by ROWNUM
이렇게 쿼리를 짰는데
이걸 null 을 없애고 .
ROLE_ADMIN ROLE_USER_MEMBER ROLE_ANONYMOUS 한줄로 나타낼 방법이없을까요 ..
by 신히
[2024.04.26 07:54:50]
ROWNUM을 없애고 GROUP BY 를 없애주세요.
WITH TBL AS (
SELECT 1 ROWNUM, NULL A1, 'ROLE_USER_MEMBER' A2, NULL A3
UNION ALL
SELECT 2, NULL, NULL, 'ROLE_ANONYMOUS'
UNION ALL
SELECT 3, 'ROLE_ADMIN', NULL, NULL
)
SELECT MAX(A1) A1
, MAX(A2) A2
, MAX(A3) A3
FROM TBL
;
by 마농
[2024.04.26 10:37:23]
SELECT MAX(CASE chldrn_role WHEN 'ROOT' THEN parnts_role END) A1
, MAX(CASE chldrn_role WHEN 'ROLE_ADMIN' THEN parnts_role END) A2
, MAX(CASE chldrn_role WHEN 'ROLE_USER_MEMBER' THEN parnts_role END) A3
FROM lettnroles_hierarchy
WHERE chldrn_role IN ('ROOT', 'ROLE_ADMIN', 'ROLE_USER_MEMBER')
;
) 버튼을 클릭하여 작성 하시면 됩니다.