如何在SELECT语句中生成订单索引值(如列表的顺序)

Suppose I have these two tables :

假设我有这两张桌子:

TABLEA          TABLEB
-----------     -----------
ID  | NAME      ID | TABLEA_ID  | NAME
1   |  ...      1  | 1          | ...
2   |           2  | 2          | ...
3   |           3  | 2          |
4   |           4  | 2          |
                5  | 3          |
                6  | 3          |
                7  | 4          | 
                8  | 2          |

I want an SQL SELECT statement that can generate such result when TABLEA.ID = TABLEB.TABLEA_ID, you can note here I don't care about grouping or ordering, I just want to generate a incremented value for each line of the same TABLEB.TABLEA_ID.

我想要一个SQL SELECT语句，它可以在表a中生成这样的结果。ID =表b。表a_id，这里我不关心分组或排序，我只想为同一个表的每一行生成一个递增的值。

ID | TABLEA_ID | ORDER_INDEX | NAME 
1  | 1         | 0           | ...  
2  | 2         | 0           | ...  
3  | 2         | 1           |      
4  | 2         | 2           |      
5  | 3         | 0           |      
6  | 3         | 1           |      
7  | 4         | 0           |      
8  | 2         | 3           |

I tried without success to use rownum in several combination of sub-selects to generate the ORDER_INDEX depending on the value in TABLEA_ID.

我尝试在几个子选择组合中使用rownum来根据TABLEA_ID中的值生成ORDER_INDEX，但没有成功。

Do you have hint to do that in plain SQL, is it even possible with plain SQL.
在纯SQL中，您是否有这样的提示，它甚至可以使用纯SQL。
Is it possible via a PL/SQL ? And how if possible ?
可以通过PL/SQL实现吗?如果可能的话?

Thank you very much in advance.

非常感谢。

3 个解决方案

#1

I believe that this is what you want:

我相信这就是你想要的:

SELECT B.ID, B.TABLEA_ID, 
       ROW_NUMBER() OVER(PARTITION BY B.TABLEA_ID ORDER BY B.ID) - 1 ORDER_INDEX,
       B.NAME -- OR A.NAME, its not clear on your question
FROM TABLEB B
LEFT JOIN TABLEA A
ON B.TABLEA_ID = A.ID

#2

Something like this:

是这样的:

SELECT
    TableB.ID,
    TableB.TableA_ID,
    ROW_NUMBER() OVER(PARTITION BY TableB.TableA_ID ORDER BY TableB.TableA_ID) AS ORDER_INDEX,
    TableB.Name
FROM
    TableA
    JOIN TableB
        ON TableA.ID=TableB.TableA_ID
ORDER BY TableB.ID

#3

How about

如何

ROW_NUMBER() OVER (PARTITION BY TABLEA_ID ORDER BY ID ASC) AS ORDER_INDEX

as the definition of ORDER_INDEX

作为ORDER_INDEX的定义

#1