| |

VerySource

 Forgot password?
 Register
Search
View: 7|Reply: 7

About number merger (urgent !!)

[Copy link]

1

Threads

3

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 China

Post time: 2020-1-30 12:40:01
| Show all posts |Read mode
The table is as follows
Id--number
pjhm--varchar
eg:
id pjhm
1 1
1 2
1 3
twenty one
twenty two
Find a sql to implement number combination based on id number,

1 1-3
2 1-2
Reply

Use magic Report

0

Threads

3

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 China

Post time: 2020-3-8 11:15:02
| Show all posts
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;
Reply

Use magic Report

0

Threads

13

Posts

10

Credits

Newbie

Rank: 1

Credits
10

 China

Post time: 2020-3-8 17:15:01
| Show all posts
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;
Reply

Use magic Report

1

Threads

3

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 China

 Author| Post time: 2020-3-8 21:00:01
| Show all posts
Write a static statement ...
Reply

Use magic Report

0

Threads

3

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 China

Post time: 2020-3-12 17:30:01
| Show all posts
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;
Reply

Use magic Report

1

Threads

3

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 China

 Author| Post time: 2020-3-16 16:00:02
| Show all posts
Didn't make it clear,
I don't want static statements
Reply

Use magic Report

0

Threads

3

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 China

Post time: 2020-3-17 00:15:02
| Show all posts
Still don't understand what statement the landlord wants?
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;
Reply

Use magic Report

0

Threads

68

Posts

47

Credits

Newbie

Rank: 1

Credits
47

 China

Post time: 2020-3-17 19:45:01
| Show all posts
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;
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2020|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list