Oracle的row_number函数

row_number函数

语法格式
row_number() over (partition by a order by b)

语法说明
将表按照a字段进行分组，然后，按照b字段进行组内排序
并给出排序的编号，排序的编号，为组内排序，独自生成一列

SELECT
    c1,
    c2,
    c3,
    ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c2) rn
FROM
    (
        SELECT
            R1.ASSAY_EQUIPMENT_ID c1,
            R1.ASSAY_ITEM_ID c2,
            T1.ASSAY_ITEM_NAME c3
        FROM
            CNF_ASSAY_ITEM_EQUI_RELA R1,
            DIC_ASSAY_ITEM T1
        WHERE
            R1.ASSAY_ITEM_ID = T1. ID
    )