SQL, который украсит каждого члена повторяющейся группы порядковым номером

Question

Я хочу украсить каждого члена каждой повторяющейся группы в таблице порядковым номером. Таким образом, для:

id    fk    scheme    code
1     23    2         1234
2     23    2         6666
3     23    2         9876
4     24    2         3421
5     24    5         erty
6     24    7         wert
7     25    2         3490
8     25    2         2389
9     25    5         erfg

Я бы увидел в своем результате

23|2|1  1234
23|2|2  6666
23|2|3  9876
24|2|1  3421
24|5|1  erty
24|7|1  wert
25|2|1  3490
25|2|2  2389
25|5|1  erfg

Таким образом, ФК в сочетании со схемой делает повторяющуюся группу. Я знаю, что у меня только до 5 в повторяющейся группе, но мой тестовый запрос допускает только 3. Последовательный номер строки не будет использоваться в качестве украшения, он должен быть 1 или 2 или 3 только потому, что они используются для поиска в кэше.

Вот SQL, который я придумал, используя синтаксис H2 Db SQL:

SELECT "identifier", "code" FROM (
SELECT CASE WHEN s1."code" IS NOT NULL AND s2."code" IS NULL AND s3."code" IS NULL 
THEN s1."ident"||'|1' 
WHEN s1."code" IS NOT NULL AND s2."code" IS NOT NULL AND s3."code" IS NULL
THEN s2."ident"||'|2' 
WHEN  s1."code" IS NOT NULL AND s2."code" IS NOT NULL AND s3."code" IS NOT NULL
THEN s3."ident"||'|3' END AS "identifier", s1."code"
FROM (select "ii"."fk", "ii"."scheme", "ii"."fk"||'|'||"ii"."scheme" AS "ident", "code"
from "inidentifier" "ii" group by "fk", "scheme", "code") s1 
LEFT OUTER JOIN
(select "ii"."fk", "ii"."scheme", "ii"."fk"||'|'||"ii"."scheme" AS "ident", "code" 
from "inidentifier" "ii" group by "fk", "scheme", "code") s2 ON s1."ident" = s2."ident"
AND s1."code" < s2."code"
LEFT OUTER JOIN
(select "ii"."fk", "ii"."scheme", "ii"."fk"||'|'||"ii"."scheme" AS "ident", "code"
from "inidentifier" "ii" group by "fk", "scheme", "code") s3 ON  s1."ident" = s2."ident"
AND s2."ident" = s3."ident" AND s1."code" < s2."code" AND s2."code" < s3."code"
ORDER BY "identifier", s1."code") "cache"
WHERE "cache"."identifier" IS NOT NULL

У меня проблема в том, что он не производит с |3 на конце, в приведенных выше примерах должно быть 1. Он заканчивается 23|2|2 9876 и 23|2|2 6666. Может быть, другой подход?

заранее спасибо

Дэвид

Answer 1

Если с любой СУБД все в порядке, естественный подход row_number():

select  *
,       row_number() over (partition by fk, scheme order by id) as rn
from    YourTable

Это вернет порядковый номер для каждой строки в (fk, scheme) группа.