Início / dba / Perguntas / 221290
Accepted
BradC
Asked: 2018-10-30 12:28:27 +0800 CST

Adicionando um ROW_NUMBER() sem coluna a ORDER BY?

  • 772

Então, estou trabalhando em um quebra-cabeça de golfe de código e preciso adicionar uma coluna "número" INT n a um resultado, mantendo a ordem atual.

Digamos que meus dados de origem sejam:

SELECT value
FROM STRING_SPLIT('one,two,three,four,five', ',')

que retorna os itens na ordem original (desejada):

value
-----
one
two
three
four
five

Se eu tentar usar ROW_NUMBER()ou RANK()for forçado a especificar um ORDER BY, para o qual valueé a única opção legal:

SELECT value, n = ROW_NUMBER() OVER(ORDER BY value)
FROM STRING_SPLIT('one,two,three,four,five',',')

Mas isso (como esperado) classifica valueem ordem alfabética em vez de deixá-lo na ordem original desejada:

value   n
------ ---
five    1
four    2
one     3
three   4
two     5

A junção a uma tabela numérica não funciona, pois sem uma WHEREcláusula obterei uma junção externa completa.

O melhor que consegui foi usar uma tabela temporária com um campo de identidade:

CREATE TABLE #argg (n INT IDENTITY(1,1), v VARCHAR(99))

INSERT #argg 
SELECT value v
FROM STRING_SPLIT('one,two,three,four,five',',')

SELECT *
FROM #argg

DROP TABLE #argg

mas isso é muito longo e irritante. Alguma ideia melhor?

sql-server order-by

4 respostas

  1. Best Answer

    A maneira canônica de fazer isso é a seguinte: ROW_NUMBER() OVER(ORDER BY (SELECT NULL)). Se você está jogando golfe, você pode tentar algo assim:

    SELECT value, n = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM STRING_SPLIT('one,two,three,four,five',',')

    Funciona para o caso simples que você postou na pergunta:

    insira a descrição da imagem aqui

    Devo dizer que não há uma garantia documentada de que o ROW_NUMBER()valor estará na ordem exata que você espera. Mas é golfe de código, então isso parece bom o suficiente.

    • 11

  2. Você não pode confiar no seu INSERTpara gerar IDENTITYvalores na ordem de sua string original. Isso pode ser o que você observa, mas isso é apenas uma coincidência de sorte e certamente não é garantido.

    O seguinte funcionará se você não tiver duplicatas:

    DECLARE @str varchar(255) = 'one,two,three,four,five';

SELECT value, n = ROW_NUMBER() OVER
  (ORDER BY CHARINDEX(',' + value + ',', ',' + @str + ','))
FROM STRING_SPLIT('one,two,three,four,five', ',')
ORDER BY n;

    Para código de golfe talvez:

    DECLARE @s char(99)='one,two,three,four,five';
SELECT value,n=RANK() OVER(ORDER BY CHARINDEX(','+value+',',','+@s+','))
FROM STRING_SPLIT('one,two,three,four,five', ',')ORDER BY n

    Se você tiver duplicatas, torna-se muito mais complexo. Algumas ideias aqui talvez:

    • 6

  3. Outra opção é usar uma sequência:

    DROP SEQUENCE IF EXISTS dbo.S;
CREATE SEQUENCE dbo.S START WITH 1;

SELECT value, NEXT VALUE FOR dbo.S 
FROM STRING_SPLIT('one,two,three,four,five', ',');

    Resultado:

    ╔═══════╦═══╗
║ one   ║ 1 ║
║ two   ║ 2 ║
║ three ║ 3 ║
║ four  ║ 4 ║
║ five  ║ 5 ║
╚═══════╩═══╝

    É irritante que STRING_SPLITfoi implementado sem uma opção de numeração embutida. Vote na mudança em https://feedback.azure.com/forums/908035-sql-server/suggestions/32902852-string-split-is-not-feature-complete

    • 5

  4. Apenas adicionando mais uma alternativa da sua pergunta. Mas acho que o pedido não é garantido,

    SELECT value v,IDENTITY(INT,1,1) AS n
INTO #argg
FROM STRING_SPLIT('one,two,three,four,five',',')

SELECT * FROM #argg
    • 2

relate perguntas