求一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.上级省份 城市------ --------------------------------------------辽宁省 沈阳市,大连市吉林省 长春市,延边市
[解决办法]
一个函数wm_concat搞定 参考上面
[解决办法]
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.上级
省份 城市
------ --------------------------------------------
辽宁省 沈阳市,大连市
吉林省 长春市,延边市