当前位置: 动力学知识库 > 问答 > 编程问答 >

oracle - Generate number in sql select statement

问题描述:

I have a query that returns some parent records and child records.

I would like to return for each child records a unique id by increment.

So if i have a record with 3 childs and 1 with 2 childs would like to return.

 Parent/Child | Parent_id | Child_id

1. Parent record, Parent_id, 0

2. child_record, Parent_Id, 1

3. child_record, Parent_Id, 2

4. child_record, Parent_Id, 3

5. Parent record, Parent_id, 0

6. child_record, Parent_Id, 1

7. child_record, Parent_Id, 2

Any ideas of how to generate the child_id from 1 and increment by 1 and then reset again for next batch of child records?

网友答案:

This query produces the child numbering contingent to their parent id:

select id_pk  -- pk; defines global sort order
     , p_c    -- node type ('parent', 'child')
     , p_id   -- parent id
     , row_number() over ( partition by p_id order by id_pk ) - 1 c_id
              -- synthetic child id. 
  from test_pc
     ;

Adjust the sorting criterion in the order by clause as needed.

Example: (order among children does not matter, just a unique local id is needed)

     , row_number() over ( partition by p_id order by p_id, p_c desc ) - 1 c_id
分享给朋友:
您可能感兴趣的文章:
随机阅读: