关于号码合并(急!! )

zxfred

表如下
Id--number
pjhm--varchar
eg:
id pjhm
1   1
1   2
1   3
2    1
2    2
求一sql实现根据id号进行号码合并 ,

1  1-3
2  1-2

ohguro

select ID,min(PJHM), max(PJHM)                          
  from (                                                  
  select ID, PJHM - rownum as c, rownum, PJHM from (   
  SELECT  * FROM (                                       
    SELECT 1 AS ID, '001' AS PJHM FROM DUAL UNION ALL   
    SELECT 1 AS ID, '002' AS PJHM FROM DUAL UNION ALL   
    SELECT 1 AS ID, '003' AS PJHM FROM DUAL UNION ALL   
    SELECT 1 AS ID, '005' AS PJHM FROM DUAL UNION ALL   
    SELECT 1 AS ID, '006' AS PJHM FROM DUAL UNION ALL   
    SELECT 2 AS ID, '001' AS PJHM FROM DUAL UNION ALL   
    SELECT 2 AS ID, '005' AS PJHM FROM DUAL UNION ALL   
    SELECT 2 AS ID, '006' AS PJHM FROM DUAL                                                  
    )order by ID,PJHM)                                 
  )group by ID,c                                       
  ORDER BY 1, 2 ;

panda_ren

create or replace function f_get_str(in_id number) return varchar2
is
v_str varchar(50);
v_result varchar(500);
cursor c_str is
    select pjhm from table1 where id=in_id ;
begin
    open c_str;
    v_result := ' ';
    loop
        fetch c_str into v_str;
        
        exit when c_str%NOTFOUND;
        v_result := v_result || trim(v_str) || '-' ;
    end loop;
    close c_str;  
    return v_result;
end f_get_str;
/
select id,f_get_str(id)
from table1
order by id;

zxfred

写个静态的语句......

ohguro

select ID,min(PJHM)||'-'||max(PJHM)                          
  from (                                                  
  select ID, PJHM - rownum as c, rownum, PJHM from (   
  SELECT  * FROM (                                       
    SELECT 1 AS ID, '001' AS PJHM FROM DUAL UNION ALL   
    SELECT 1 AS ID, '002' AS PJHM FROM DUAL UNION ALL   
    SELECT 1 AS ID, '003' AS PJHM FROM DUAL UNION ALL   
    SELECT 1 AS ID, '005' AS PJHM FROM DUAL UNION ALL   
    SELECT 1 AS ID, '006' AS PJHM FROM DUAL UNION ALL   
    SELECT 2 AS ID, '001' AS PJHM FROM DUAL UNION ALL   
    SELECT 2 AS ID, '005' AS PJHM FROM DUAL UNION ALL   
    SELECT 2 AS ID, '006' AS PJHM FROM DUAL                                                
    )order by ID,PJHM)                                 
  )group by ID,c                                       
  ORDER BY 1, 2 ;

zxfred

没说清楚,
我不想要静态语句

ohguro

还是不明白楼主想要什么样的语句？
select ID,min(PJHM)||'-'||max(PJHM)
from ( select ID, PJHM - rownum as c, rownum, PJHM from (
SELECT * FROM table_name order by ID,PJHM)
)group by ID,c
ORDER BY 1, 2 ;

whbilly

select ID,min(PJHM)||'-'||max(PJHM)
from ( select ID, PJHM - rownum as c, rownum, PJHM from (
SELECT * FROM table_name order by ID,PJHM)
)group by ID,c
ORDER BY 1, 2 ;