这是我的语句:
select t.full_name, a.a_name,t.dept_id
from department t left join dept_area da on t.dept_id = da.dept_id
left join base_area a on da.a_index = a.a_index
where 1=1
order by t.dept_id
其结果是2跳:
北京开发部 华北 1
北京开发部 东北 1我想要这样的一条结果: 北京开发部 华北,东北 1
select t.full_name, a.a_name,t.dept_id
from department t left join dept_area da on t.dept_id = da.dept_id
left join base_area a on da.a_index = a.a_index
where 1=1
order by t.dept_id
其结果是2跳:
北京开发部 华北 1
北京开发部 东北 1我想要这样的一条结果: 北京开发部 华北,东北 1
SELECT T.FULL_NAME, WM_COMCAT(A.A_NAME), T.DEPT_ID
FROM DEPARTMENT T
LEFT JOIN DEPT_AREA DA ON T.DEPT_ID = DA.DEPT_ID
LEFT JOIN BASE_AREA A ON DA.A_INDEX = A.A_INDEX
WHERE 1 = 1
GROUP BY T.FULL_NAME,T.DEPT_ID
ORDER BY T.DEPT_ID
;
from department t left join dept_area da on t.dept_id = da.dept_id
left join base_area a on da.a_index = a.a_index
where 1=1
group by t.dept_id order by t.dept_id
或者
select distinct t.full_name, wmsys.wm_concat(a.a_name)over(partition by t.dept_id),t.dept_id
from department t left join dept_area da on t.dept_id = da.dept_id
left join base_area a on da.a_index = a.a_index
from department t left join dept_area da on t.dept_id = da.dept_id
left join base_area a on da.a_index = a.a_index
where 1=1
group by t.dept_id order by t.dept_id