데이터베이스 질문과 답변
티베로 정렬조건문을 mysql에서 사용 가능 여부
0
2
925
by ksbgenius [2017.01.16 15:27:51]
현재 티베로에서 사용하는 쿼리입니다.
SELECT *
FROM
dept_info
WHERE
dept_type!='1' START WITH UPR_DEPT_CODE = '6290000' CONNECT BY PRIOR DEP_CODE =
UPR_DEPT_CODE ORDER SIBLINGS BY to_number(DEPT_RANK);
이 쿼리는 부서를 정렬하는 쿼리 입니다.
|
샘플로 몇개 데이터만 올려봤습니다.
티베로 쿼리를 mysql에서 사용해서 정렬을 하고자 합니다...
도움을 주시면 감사하겠습니다.
by 마농
[2017.01.18 13:24:40]
-- 1 Level -- SELECT a.* , a.dept_rank AS dept_rank_full FROM dept_info a WHERE a.use_yn = '1' AND a.dept_type != '1' AND a.upr_dept_code = '6290000' UNION ALL -- 2 Level -- SELECT b.* , CONCAT( a.dept_rank , b.dept_rank ) dept_rank_full FROM dept_info a INNER JOIN dept_info b ON b.upr_dept_code = a.dep_code WHERE a.use_yn = '1' AND b.use_yn = '1' AND a.dept_type != '1' AND b.dept_type != '1' AND a.upr_dept_code = '6290000' -- UNION ALL ---- 3 Level -- --SELECT c.* -- , CONCAT( a.dept_rank -- , b.dept_rank -- , c.dept_rank -- ) dept_rank_full -- FROM dept_info a -- INNER JOIN dept_info b ON b.upr_dept_code = a.dep_code -- INNER JOIN dept_info c ON c.upr_dept_code = b.dep_code -- WHERE a.use_yn = '1' -- AND b.use_yn = '1' -- AND c.use_yn = '1' -- AND a.dept_type != '1' -- AND b.dept_type != '1' -- AND c.dept_type != '1' -- AND a.upr_dept_code = '6290000' ORDER BY dept_rank_full ;
by 마농
[2017.01.19 17:05:30]
SELECT m.* , @p := dep_code , (SELECT GROUP_CONCAT( (SELECT CASE WHEN (@p := upr_dept_code) = upr_dept_code THEN dept_rank END FROM test4 WHERE dep_code = @p AND sprm_dept_code = '6290000' ) ORDER BY lv DESC) FROM (SELECT 1 lv UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) a WHERE @p != 0 ) dept_rank_full FROM test4 m , (SELECT @p := 0) a WHERE sprm_dept_code = '6290000' ;
) 버튼을 클릭하여 작성 하시면 됩니다.