当前位置: 代码迷 >> Oracle管理 >> 求一SQL 语句 多谢大家
  详细解决方案

求一SQL 语句 多谢大家

热度:463   发布时间:2016-04-24 05:21:48.0
求一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.上级省份   城市------ --------------------------------------------辽宁省 沈阳市,大连市吉林省 长春市,延边市
  相关解决方案