急求---这个sql语句如何写？

[weihai27]

如何用SQL语句 将左面的数据 统计成右面表格的数据？
数据库为ORACLE11G

求各位大神指教。

[bw555]

很有意思的需求，应该是利用connect by start with的树形查询能实现，一会给你写下吧

[bw555]

按你给定的数据测试通过，到实际环境执行下试试吧

SQL> WITH Table1 AS(
  2  select 'A' A,1 B FROM DUAL UNION ALL
  3  select 'A' A,2 B FROM DUAL UNION ALL
  4  select 'A' A,3 B FROM DUAL UNION ALL
  5  select 'A' A,5 B FROM DUAL UNION ALL
  6  select 'A' A,6 B FROM DUAL UNION ALL
  7  select 'B' A,1 B FROM DUAL UNION ALL
  8  select 'B' A,2 B FROM DUAL UNION ALL
  9  select 'C' A,1 B FROM DUAL UNION ALL
 10  select 'C' A,2 B FROM DUAL UNION ALL
 11  select 'C' A,3 B FROM DUAL UNION ALL
 12  select 'C' A,4 B FROM DUAL UNION ALL
 13  select 'C' A,8 B FROM DUAL UNION ALL
 14  select 'C' A,9 B FROM DUAL UNION ALL
 15  select 'C' A,10 B FROM DUAL
 16  ),T AS (
 17  SELECT A,B,LAG(B,1,B-2)OVER(PARTITION BY A ORDER BY B) PRE
 18  FROM Table1
 19  ),T1 AS (
 20  SELECT A,CONNECT_BY_ROOT(B)||'-'||B B FROM T
 21  WHERE CONNECT_BY_ISLEAF=1
 22  START WITH B-PRE>1
 23  CONNECT BY PRIOR A=A AND PRIOR B+1=B
 24  )
 25  SELECT A,TO_CHAR(WMSYS.WM_CONCAT(B)) B
 26  FROM T1 GROUP BY A;

A
-
B
--------------------------------------------------------------------------------
A
1-3,5-6

B
1-2

C
1-4,8-10

[小灰狼W]

select a, wm_concat(b_range) from (select a, min(b) || '-' || max(b) b_range from (select a, b, dense_rank() over(partition by a order by b) dr from t) group by a, dr - b) group by a

[bw555]

引用 4 楼 wildwave 的回复:

select a, wm_concat(b_range) from (select a, min(b) || '-' || max(b) b_range from (select a, b, dense_rank() over(partition by a order by b) dr from t) group by a, dr - b) group by a

妙的很啊

[tracy敬]

引用 2 楼 bw555 的回复:

按你给定的数据测试通过，到实际环境执行下试试吧

SQL> WITH Table1 AS(
  2  select 'A' A,1 B FROM DUAL UNION ALL
  3  select 'A' A,2 B FROM DUAL UNION ALL
  4  select 'A' A,3 B FROM DUAL UNION ALL
  5  select 'A' A,5 B FROM DUAL UNION ALL
  6  select 'A' A,6 B FROM DUAL UNION ALL
  7  select 'B' A,1 B FROM DUAL UNION ALL
  8  select 'B' A,2 B FROM DUAL UNION ALL
  9  select 'C' A,1 B FROM DUAL UNION ALL
 10  select 'C' A,2 B FROM DUAL UNION ALL
 11  select 'C' A,3 B FROM DUAL UNION ALL
 12  select 'C' A,4 B FROM DUAL UNION ALL
 13  select 'C' A,8 B FROM DUAL UNION ALL
 14  select 'C' A,9 B FROM DUAL UNION ALL
 15  select 'C' A,10 B FROM DUAL
 16  ),T AS (
 17  SELECT A,B,LAG(B,1,B-2)OVER(PARTITION BY A ORDER BY B) PRE
 18  FROM Table1
 19  ),T1 AS (
 20  SELECT A,CONNECT_BY_ROOT(B)||'-'||B B FROM T
 21  WHERE CONNECT_BY_ISLEAF=1
 22  START WITH B-PRE>1
 23  CONNECT BY PRIOR A=A AND PRIOR B+1=B
 24  )
 25  SELECT A,TO_CHAR(WMSYS.WM_CONCAT(B)) B
 26  FROM T1 GROUP BY A;

A
-
B
--------------------------------------------------------------------------------
A
1-3,5-6

B
1-2

C
1-4,8-10

大牛

[Iversonbi]

引用 4 楼 wildwave 的回复:

select a, wm_concat(b_range) from (select a, min(b) || '-' || max(b) b_range from (select a, b, dense_rank() over(partition by a order by b) dr from t) group by a, dr - b) group by a

怎么在mysql 中无法运行这sql

[bw555]

引用 6 楼 Iversonbi 的回复:

[quote=引用 4 楼 wildwave 的回复:] select a, wm_concat(b_range) from (select a, min(b) || '-' || max(b) b_range from (select a, b, dense_rank() over(partition by a order by b) dr from t) group by a, dr - b) group by a

怎么在mysql 中无法运行这sql[/quote] mysql中没分析函数的用法吧，想在mysql实现此效果，需要表自连接实现

[qq_25043719]

真是高手啊! 妙啊