求一SQL 语句 谢谢大家！
表A

id 省市 上级
 
1 辽宁省 0
2 沈阳市 1
3 大连市 1
4 吉林省 0
5 长春市 4
6 延边市 4

求一Pl/sql语句 查完结果是2行记录

  辽宁省 沈阳市,大连市
  吉林省 长春市，延边市



------解决方案--------------------
--wm_concat()函数用法
with tb as (
select '王' name,'数学' course, 11 type, '2011-11-30 18:11:00' time from dual union all
select '王' , '数学', 11, '2011-11-30 18:11:00' from dual union all
select '王' , '语文', 12, '2011-10-30 18:11:00' from dual union all
select '张' , '数学', 11, '2011-11-30 18:11:00' from dual 
) 

select tt.name, wm_concat(tt.km), wm_concat(tt.sj)
from (
select name,
to_char(course),
to_char(t.a) as km,
to_char(type) || '/' || to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'), 'mm.dd') as sj
from (select name, course, count(1) a, time, type
from tb
group by name，course，time，type) t
) tt
 group by tt.name
------解决方案--------------------
 
WITH temp_tab1 AS 
(
 SELECT 1 AS ID , '辽宁省' AS "省份" , '0' AS "上级" FROM dual UNION ALL 
 SELECT 2 AS ID , '沈阳市' AS "省份" , '1' AS "上级" FROM dual UNION ALL 
 SELECT 3 AS ID , '大连市' AS "省份" , '1' AS "上级" FROM dual UNION ALL 
 SELECT 4 AS ID , '吉林省' AS "省份" , '0' AS "上级" FROM dual UNION ALL 
 SELECT 5 AS ID , '长春市' AS "省份" , '4' AS "上级" FROM dual UNION ALL 
 SELECT 6 AS ID , '延边市' AS "省份" , '4' AS "上级" FROM dual
) 

SELECT /*CASE WHEN a.上级 = 0 THEN a.省份 ELSE NULL END AS COL1 ,
CASE WHEN A.上级 = b.ID THEN A.省份 ELSE NULL END AS col2 */
a.省份 ,
WMSYS.WM_CONCAT(CASE WHEN B.上级 = A.ID THEN B.省份 ELSE NULL END) AS "城市"
FROM TEMP_TAB1 A,TEMP_TAB1 B 
GROUP BY a.省份
HAVING WMSYS.WM_CONCAT(CASE WHEN B.上级 = A.ID THEN B.省份 ELSE NULL END) IS NOT NULL
------解决方案--------------------

SQL code

 
WITH temp_tab1 AS 
(
 SELECT 1 AS ID , '辽宁省' AS "省份" , '0' AS "上级"  FROM dual UNION ALL 
 SELECT 2 AS ID , '沈阳市' AS "省份" , '1' AS "上级"  FROM dual UNION ALL 
 SELECT 3 AS ID , '大连市' AS "省份" , '1' AS "上级"  FROM dual UNION ALL 
 SELECT 4 AS ID , '吉林省' AS "省份" , '0' AS "上级"  FROM dual UNION ALL 
 SELECT 5 AS ID , '长春市' AS "省份" , '4' AS "上级"  FROM dual UNION ALL 
 SELECT 6 AS ID , '延边市' AS "省份" , '4' AS "上级"  FROM dual  
) 

SELECT /*CASE WHEN a.上级 = 0 THEN a.省份 ELSE NULL END AS COL1 ,
       CASE WHEN A.上级 = b.ID THEN A.省份 ELSE NULL END AS col2 */
     a.省份 ,
     WMSYS.WM_CONCAT(CASE WHEN B.上级 = A.ID THEN B.省份 ELSE NULL END) AS "城市"
FROM TEMP_TAB1 A,TEMP_TAB1 B 
GROUP BY a.省份
HAVING WMSYS.WM_CONCAT(CASE WHEN B.上级 = A.ID THEN B.省份 ELSE NULL END) IS NOT NULL

------解决方案--------------------
SQL code

WITH t AS  
(
 SELECT 1 AS ID , '辽宁省' AS "省份" , '0' AS "上级" FROM dual 
 UNION ALL  
 SELECT 2 AS ID , '沈阳市', '1' FROM dual 
 UNION ALL  
 SELECT 3 AS ID , '大连市', '1' FROM dual 
 UNION ALL  
 SELECT 4 AS ID , '吉林省', '0' FROM dual 
 UNION ALL  
 SELECT 5 AS ID , '长春市', '4' FROM dual 
 UNION ALL  
 SELECT 6 AS ID , '延边市', '4' FROM dual   
)
select t.省份, t1.城市
  from t,
       (select 上级, wm_concat(省份) 城市
          from t
         where 上级 <> '0'
         group by 上级) t1
 where t.id = t1.上级

省份   城市
------ --------------------------------------------
辽宁省 沈阳市,大连市
吉林省 长春市,延边市