Inicialmente, acho que preciso explicar o que estou chamando de subsequência numérica. Imaginem duas tabelas, uma mestre e uma de detalhe. Pedidos e itens de pedido, por exemplo. A tabela de Itens é dependente da tabela de pedidos. Precisa dela para existir. Nesses casos, é comum que a tabela de itens possua uma chave primária composta, onde a chave estrangeira para pedido faça parte da composição. Adicionalmente a isso, é criado um campo sequencial que servirá para diferenciar a chave dos itens dentro de um mesmo pedido. A chave estrangeira do pedido e esse campo sequencial formam a chave primária do item, mas o campo sequencial se repete de um pedido a outro. Ou seja, dentro de cada pedido, os itens possuem sequencial de 1 a N, onde N é o número de itens do pedido.
Exemplo:
Tabela Itens Pedido
cod_pedido | sequencial | cod_produto | ...
1 1 1236
1 2 1421
1 3 7633
1 4 1187
------------------------------------------
2 1 1236
2 2 1421
2 3 7633
------------------------------------------
3 1 1236
3 2 4866
3 3 7633
3 4 1187
------------------------------------------
Note que sequencial e cod_pedido se repetem, mas os dois juntos, nunca. Alem disso, sequencial é sempre iniciado em 1, e incrementado item a item dentro do mesmo pedido.
No nível da aplicação é fácil resolver: para cada pedido, eu inicio uma variável que é incrementada a cada item adicionado. Pra explicar é um pouco complicado e para montar um SQL que gere esse campo sequencial a partir de uma consulta no banco de dados, aí é que é complicado mesmo.
Infelizmente, não existe uma forma padronizada de resolver um problema como esse. Na verdade, a solução para esse problema se assemelha muito à geração de sequência numérica em uma consulta SQL. Tudo vai depender, mais uma vez, do banco de dados que você está usando. Então, como de costume, vou descrever a solução para alguns bancos por ordem de sequelas que cada uma pode gerar.
Oracle
São nessas horas mais difíceis que o Oracle nos enche de orgulho. A solução aqui é tão simples quanto a do post anterior: a função ROW_NUMBER() sozinha resolve o problema. Para tanto, é preciso definir como o Oracle vai particionar o resultado do ROW_NUMBER() através da cláusula PARTITION BY. Seque o SQL:
SELECT cod_pedido, ROW_NUMBER() OVER (PARTITION BY cod_pedido, cod_funcionario ORDER BY nome_produto) AS sequencia, cod_produto
FROM itens_pedido ORDER BY cod_pedido, cod_produto
Maiores detalhes a respeito dessa função, consulte a farta documentação do Oracle em: http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions122.htm
Microsoft SQL Server (versão 2005 ou superior)
Como já dito anteriormente, o SQL Server, a partir de versão 2005, também possui uma função ROW_NUMBER() e com a mesma sintaxe do Oracle! Com isso, a solução é também a mesma do Oracle:
SELECT cod_pedido, ROW_NUMBER() OVER (PARTITION BY cod_pedido, cod_funcionario ORDER BY nome_produto) AS sequencia, cod_produto
FROM itens_pedido ORDER BY cod_pedido, cod_produto
Maiores detalhes a respeito dessa função, consulte a documentação da Microsoft, que é um pouco diferente da Oracle, mas igualmente bem detalhada: http://msdn.microsoft.com/en-us/library/ms186734.aspx
Se seu banco de dados for SQL Server 2000, sugiro fortemente instalar uma versão superior, como a 2005 ou a 2008. Agora, se não for possível fazer essa atualização, você pode pedir demissão e fazer vestibular para biologia ou tentar a alternativa que descrevo mais abaixo.
SQLite
O SQLite, infelizmente não possui uma função ROW_NUMBER(). Nos aproveitaremos mais uma vez de uma característica bem peculiar do SQLite: Todos os registros de qualquer tabela possuem um rowid, que é um sequencial numérico do registro que segue a sequência de inserção nessa tabela. Dessa forma, podemos criar uma tabela temporária, construída a partir da consulta que queremos adicionar o sequencial. Se inserirmos os registros na ordem desejada, cada um receberá o rowid sequencial, mas ainda não o sequencial que queremos. Inicialmente, vamos criar essa tabela temporária nos aproveitando de outra característica do SQLite: você pode facilmente criar uma tabela a partir de uma consulta.
CREATE TEMP TABLE tmp_itens AS SELECT * FROM itens_pedido ORDER BY cod_pedido, cod_produto;
Note que a tabela é criada a partir do mesmo select anterior, mantendo-se inclusive a ordenação. Note também o "TEMP" na instrução SQL. Isso indica que a tabela criada é temporária e não será visível para outras conexões. A partir de agora, temos uma tabela temporária, com os registros desejados, e com um rowid sequencial, indo de 1 ao número de registros selecionados. Isso ainda não é o que precisamos.
cod_pedido | rowid | cod_produto | ...
1 1 1236
1 2 1421
1 3 7633
1 4 1187
-------------------------------------
2 5 1236
2 6 1421
2 7 7633
-------------------------------------
3 8 1236
3 9 4866
3 10 7633
3 11 1187
-------------------------------------
Note que o campo rowid é um sequencial, mas ele não inicia em 1 para todos os pedidos. Para o primeiro pedido, inicia em 1, para o segundo, em 5 e para o terceiro, em 8. No entando, se subtrairmos o rowid pelo menor rowid dentro do mesmo pedido e somarmos com 1, teremos o resultado esperado. Para chegarmos a isso, faremos uso de outro recurso do SQL: o nested select ou subselect. Eis a consulta para conseguir o menor rowid para cada pedido:
SELECT cod_pedido, MIN(rowid) AS min_rowid FROM tmp_itens GROUP BY cod_pedido
Usando essa consulta como um subselect da consulta principal, teremos:
SELECT itp.cod_pedido, 1 + itp.rowid - tmp.min_rowid AS sequencial, itp.cod_produto
FROM tmp_itens itp
JOIN (SELECT cod_pedido, MIN(rowid) AS min_rowid FROM tmp_itens GROUP BY cod_pedido) tmp
ON tmp.cod_pedido = itp.cod_pedido
Nessa consulta, a consulta anterior que retorna os menores rowids para cada pedido, está entre parenteses e seu resultado será tratado na consulta principal como se fosse uma tabela, que no caso, estamos chamando de tmp. Essa não é uma forma muito limpa de se resolver, mas o resultado dessa consulta é exatamente o que precisamos. Podemos organizar o código criando outra tabela temporária a partir do resultado da primeira. Mas isso não significará ganho de performance e ainda lhe obrigará a apagar essas duas tabelas temporárias. Em SQLite, as tabelas temporárias existem apenas para a conexão que as criou, e desaparecerão quando a conexão for encerrada. Mesmo assim, é importante apagá-las (com DROP TABLE) sempre que usadas para evitar erros, caso tente-se criar novamente a mesma tabela.
Microsoft SQL Server (versões anteriores à 2005)
Se você está realmente interessado nessa seção, é porque você ainda não foi convencido a atualizar o SQL Server ou ainda não argumentou o suficiente para convencer seus superiores a isso. Dessa forma, a primeira coisa a se fazer é enviar um email aos interessados avisando que vai demorar para resolver o problema porque a versão do SQL está desatualizada. Mas que não se preocupem porque essa demora no desenvolvimento não se equipara às perdas por falta de desempenho por se usar um banco de dados tão velho e problemático. Drama a parte, aí vai a solução:
A solução em versões antigas do SQL Server, é semelhante ao SQLite, sendo que um pouco mais complicado. Primeiro porque o SQL Server não gera um "rowid" automaticamente. Segundo, porque não é possível criar uma tabela a partir de uma consulta como no SQLite. Por isso precisaremos criar uma tabela com os campos necessários, e adicionar um campo identity para gerar o sequencial. Podemos também criar a tabela como temporária, que em SQL Server, basta nomear a tabela iniciando dom "#".
CREATE TABLE #tmp_itens (
sequencia INTEGER IDENTITY(1,1),
cod_pedido INTEGER,
cod_produto INTEGER
);
Depois de criar a tabela, inserimos os registros a partir da mesma consulta que queremos adicionar o sequencial. O campo do tipo identity gera um sequencial automaticamente, ou seja, não precisamos passar valor para ele. Basta inserirmos os registros na ordem desejada e cada um receberá o sequencial único.
INSERT INTO #tmp_itens(cod_pedido, cod_produto)
SELECT cod_pedido, cod_produto
FROM itens_pedido
ORDER BY cod_pedido, cod_produto;
Com isso, temos uma tabela temporária, com os registros desejados, e com o campo sequencia que é quase aquilo que queremos. Para gerarmos o resultado que esperamos, fazemos da mesma forma como fizemos em SQLite: usamos um subselect
SELECT itp.cod_pedido, 1 + itp.rowid - tmp.min_rowid AS sequencial, itp.cod_produto
FROM #tmp_itens itp
JOIN (SELECT cod_pedido, MIN(rowid) AS min_rowid
FROM #tmp_itens
GROUP BY cod_pedido
) tmp
ON tmp.cod_pedido = itp.cod_pedido
FROM #tmp_itens itp
JOIN (SELECT cod_pedido, MIN(rowid) AS min_rowid
FROM #tmp_itens
GROUP BY cod_pedido
) tmp
ON tmp.cod_pedido = itp.cod_pedido
Note que, como não é possível criar a tabela temporária com base no resultado da consulta, como no SQLite, é preciso criar a tabela com todos os campos que se deseja ou adicionar um outro JOIN com itens_pedido nessa consulta para se obter todos os campos que se deseja.
Por fim, lembro mais uma vez que é importante, apagar a tabela temporária após utilizar o retorno da consulta. Da mesma forma como no SQLite, as tabelas temporárias no SQLServer existem apenas para a conexão que as criou, e desaparecerão quando a conexão for encerrada, mas antes disso, podem gerar erros caso tente-se criar novamente a mesma tabela.
Outros bancos
Novamente, listei aqui as soluções nos bancos de dados em que tenho mais experiência. Em outros bancos, provavelmente a solução será uma variação de uma dessas que apresentei aqui. Se alguém conhecer algum solução para outro banco de dados, ou uma solução melhor do que as apresentadas aqui, enviem-me que terei prazer em atualizar esse post.
