|
oracle
SELECT x.cd_seq AS cdSeq
, x.p_cd_seq AS pCdSeq
, x.cd_name AS cdName
, x.cd_value AS cdValue
, x.description AS description
, x.sort_no AS sortNo
, x.type_img_url AS typeImgUrl
, (SELECT COUNT (*) FROM to_metadata m, to_datatype h where m.datatype_seq = h.datatype_seq and h.type_category = x.cd_name) cnt
FROM (
SELECT cd_seq, p_cd_seq, cd_name, cd_value, description, sort_no, level lv, use_yn, type_img_url
FROM to_datatype_category
WHERE 1 = 1
AND cd_value IN('PART', 'PTHE', 'PBOK', 'PDAT', 'PPAT', 'PRPT', 'PMLT', 'PETC', 'PPRE')
CONNECT BY PRIOR cd_seq = p_cd_seq
START WITH cd_seq = 0
ORDER SIBLINGS BY sort_no
) x
WHERE x.lv = 2
postgresql
SELECT x.cd_seq AS cdSeq
, x.p_cd_seq AS pCdSeq
, x.cd_name AS cdName
, x.cd_value AS cdValue
, x.description AS description
, x.sort_no AS sortNo
, x.type_img_url AS typeImgUrl
, (SELECT COUNT (*) FROM to_metadata m, to_datatype h where m.datatype_seq = h.datatype_seq and h.type_category = x.cd_name) cnt
FROM (
WITH RECURSIVE a AS (
SELECT cd_seq, p_cd_seq, cd_name, cd_value, description, sort_no, 1 lv, use_yn, type_img_url
FROM to_datatype_category
WHERE 1 = 1
AND cd_seq = 0
AND cd_value IN('PART', 'PTHE', 'PBOK', 'PDAT', 'PPAT', 'PRPT', 'PMLT', 'PETC', 'PPRE')
UNION ALL
SELECT d.cd_seq, d.p_cd_seq, d.cd_name, d.cd_value, d.description, d.sort_no, a.lv+ 1, d.use_yn, d.type_img_url
FROM to_datatype_category d
JOIN a ON a.cd_seq = d.p_cd_seq )
SELECT * FROM a order by sort_no, cd_seq
) x
WHERE x.lv = 2
이런식으로 변경하였는데 값이 다르게 나오는데
어디서 잘못됬는지 찾기가 어렵습니다.
한번 봐주실수 있으신가요
|
