registros - select sort sql
Ordenar cadena como número en el servidor sql (9)
Tratar:
select invoiceid ... order by Convert(decimal(18, 2), REPLACE(invoiceid, ''-'', ''.''))
Tengo una columna que contiene datos como este. los guiones indican copias múltiples de la misma factura y deben ordenarse en orden ascendente
790711
790109-1
790109-11
790109-2
Tengo que ordenarlo en orden creciente por este número, pero como se trata de un campo varchar, ordena en orden alfabético como este
790109-1
790109-11
790109-2
790711
para arreglar esto intenté reemplazar el - (tablero) con vacío y luego lanzarlo como un número y luego ordenarlo
select cast(replace(invoiceid,''-'','''') as decimal) as invoiceSort...............order by invoiceSort asc
mientras esto es mejor y ordena esto
invoiceSort
790711 (790711) <-----this is wrong now as it should come later than 790109
790109-1 (7901091)
790109-2 (7901092)
790109-11 (79010911)
Alguien me sugirió que dividiera el ID de factura en el - (guión) y ordenara en las 2 partes divididas
like =====> order by split1 asc,split2 asc (790109,1)
lo cual funcionaría, pero ¿cómo dividiría la columna?
Las diversas funciones divididas en Internet son aquellas que devuelven una tabla, mientras que en este caso requeriría una función escalar.
¿Hay algún otro enfoque que pueda usarse? Los datos se muestran en la vista de cuadrícula y la vista de cuadrícula no admite la ordenación en 2 columnas de manera predeterminada (puedo implementarlo :)) así que si hay enfoques más simples, estaría muy bien.
EDITAR : gracias por todas las respuestas. Si bien todas las respuestas son correctas, he elegido la respuesta que me permitió incorporar estas columnas en la ordenación GridView con re factorización mínima de las consultas sql.
El uso juicioso de REVERSE , CHARINDEX y SUBSTRING nos permite obtener lo que queremos. He utilizado nombres de columnas con suerte explicativos en mi código a continuación para ilustrar lo que está sucediendo.
Configurar datos de muestra:
DECLARE @Invoice TABLE (
InvoiceNumber nvarchar(10)
);
INSERT @Invoice VALUES
(''790711'')
,(''790709-1'')
,(''790709-11'')
,(''790709-21'')
,(''790709-212'')
,(''790709-2'')
SELECT * FROM @Invoice
Data de muestra:
InvoiceNumber
-------------
790711
790709-1
790709-11
790709-21
790709-212
790709-2
Y aquí está el código. Tengo una sensación persistente de que las expresiones finales podrían simplificarse.
SELECT
InvoiceNumber
,REVERSE(InvoiceNumber)
AS Reversed
,CHARINDEX(''-'',REVERSE(InvoiceNumber))
AS HyphenIndexWithinReversed
,SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX(''-'',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))
AS ReversedWithoutAffix
,SUBSTRING(InvoiceNumber,1+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX(''-'',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS AffixIncludingHyphen
,SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX(''-'',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS AffixExcludingHyphen
,CAST(
SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX(''-'',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS int)
AS AffixAsInt
,REVERSE(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX(''-'',REVERSE(InvoiceNumber)),LEN(InvoiceNumber)))
AS WithoutAffix
FROM @Invoice
ORDER BY
-- WithoutAffix
REVERSE(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX(''-'',REVERSE(InvoiceNumber)),LEN(InvoiceNumber)))
-- AffixAsInt
,CAST(
SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX(''-'',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
AS int)
Salida:
InvoiceNumber Reversed HyphenIndexWithinReversed ReversedWithoutAffix AffixIncludingHyphen AffixExcludingHyphen AffixAsInt WithoutAffix
------------- ---------- ------------------------- -------------------- -------------------- -------------------- ----------- ------------
790709-1 1-907097 2 907097 -1 1 1 790709
790709-2 2-907097 2 907097 -2 2 2 790709
790709-11 11-907097 3 907097 -11 11 11 790709
790709-21 12-907097 3 907097 -21 21 21 790709
790709-212 212-907097 4 907097 -212 212 212 790709
790711 117097 0 117097 0 790711
Tenga en cuenta que todo lo que realmente necesita es la cláusula ORDER BY , el resto es solo para mostrar mi funcionamiento, que es el siguiente:
- Invierta la cuerda, encuentre el guión, obtenga la subcadena después del guión, invierta esa parte: este es el número sin ningún afijo
- La longitud de (el número sin ningún afijo) nos dice cuántos caracteres debe caber desde el principio para obtener el afijo incluido el guión. Coloque un carácter adicional para obtener solo la parte numérica y conviértalo a
int. Afortunadamente tenemos un descanso de SQL Server en que esta conversión da cero para una cadena vacía. - Finalmente, al obtener estas dos piezas, hacemos un simple
ORDER BY(el número sin ningún afijo) y luego por (el valor numérico del afijo). Este es el orden final que buscamos.
El código sería más conciso si SQL Server nos permitiera decir SUBSTRING(value, start) para obtener la cadena comenzando en ese punto, pero no es así, entonces tenemos que decir SUBSTRING(value, start, LEN(value)) mucho.
Mi version:
declare @Len int
select @Len = (select max (len (invoiceid) - charindex ( ''-'', invoiceid))-1 from MyTable)
select
invoiceid ,
cast (SUBSTRING (invoiceid ,1,charindex ( ''-'', invoiceid )-1) as int) * POWER (10,@Len) +
cast (right(invoiceid, len (invoiceid) - charindex ( ''-'', invoiceid) ) as int )
from MyTable
Puede implementar esto como una nueva columna en su tabla:
ALTER TABLE MyTable ADD COLUMN invoice_numeric_id int null
GO
declare @Len int
select @Len = (select max (len (invoiceid) - charindex ( ''-'', invoiceid))-1 from MyTable)
UPDATE TABLE MyTable
SET invoice_numeric_id = cast (SUBSTRING (invoiceid ,1,charindex ( ''-'', invoiceid )-1) as int) * POWER (10,@Len) +
cast (right(invoiceid, len (invoiceid) - charindex ( ''-'', invoiceid) ) as int )
Una forma es dividir InvoiceId en sus partes, y luego ordenar las partes. Aquí utilizo una tabla derivada, pero también podría hacerse con un CTE o una tabla temporal.
select InvoiceId, InvoiceId1, InvoiceId2
from
(
select
InvoiceId,
substring(InvoiceId, 0, charindex(''-'', InvoiceId, 0)) as InvoiceId1,
substring(InvoiceId, charindex(''-'', InvoiceId, 0)+1, len(InvoiceId)) as InvoiceId2
FROM Invoice
) tmp
order by
cast((case when len(InvoiceId1) > 0 then InvoiceId1 else InvoiceId2 end) as int),
cast((case when len(InvoiceId1) > 0 then InvoiceId2 else ''0'' end) as int)
En lo anterior, InvoiceId1 e InvoiceId2 son los componentes de InvoiceId . La select externa incluye las partes, pero solo con fines de demostración; no es necesario que haga esto en su selección.
La tabla derivada (la select interna) toma el InvoiceId así como las partes componentes. La forma en que funciona es esta:
- Cuando hay un guion en
InvoiceId,InvoiceId1contendrá la primera parte del número eInvoiceId2contendrá el segundo. - Cuando no hay un guion,
InvoiceId1estará vacío eInvoiceId2contendrá el número completo.
El segundo caso anterior (sin guiones) no es óptimo porque, idealmente, InvoiceId1 contendría el número e InvoiceId2 estaría vacío. Para que la selección interna funcione de manera óptima, disminuiría la legibilidad de la selección. Elegí el enfoque no óptimo, más legible, ya que es lo suficientemente bueno para permitir la clasificación.
Esta es la razón por la cual la cláusula ORDER BY prueba la longitud; necesita manejar los dos casos anteriores.
Demostración en SQL Fiddle
Prueba este -
Consulta:
DECLARE @Invoice TABLE (InvoiceNumber VARCHAR(10))
INSERT @Invoice
VALUES
(''790711'')
, (''790709-1'')
, (''790709-21'')
, (''790709-11'')
, (''790709-211'')
, (''790709-2'')
;WITH cte AS
(
SELECT
InvoiceNumber
, lenght = LEN(InvoiceNumber)
, delimeter = CHARINDEX(''-'', InvoiceNumber)
FROM @Invoice
)
SELECT InvoiceNumber
FROM cte
CROSS JOIN (
SELECT repl = MAX(lenght - delimeter)
FROM cte
WHERE delimeter != 0
) mx
ORDER BY
SUBSTRING(InvoiceNumber, 1, ISNULL(NULLIF(delimeter - 1, -1), lenght))
, RIGHT(REPLICATE(''0'', repl) + SUBSTRING(InvoiceNumber, delimeter + 1, lenght), repl)
Salida:
InvoiceNumber
-------------
790709-1
790709-2
790709-11
790709-21
790709-211
790711
Hay muchas buenas respuestas aquí, pero creo que esta podría ser la orden más compacta por cláusula que sea efectiva:
SELECT *
FROM Invoice
ORDER BY LEFT(InvoiceId,CHARINDEX(''-'',InvoiceId+''-''))
,CAST(RIGHT(InvoiceId,CHARINDEX(''-'',REVERSE(InvoiceId)+''-''))AS INT)DESC
Demostración: - SQL Fiddle
Tenga en cuenta que agregué la versión ''790709'' a mi prueba, ya que algunos de los métodos enumerados aquí no tratan la versión sin sufijo como menor que las versiones con sufijo.
Si su invoiceID varía en longitud, antes del ''-'' que es, entonces necesitaría:
SELECT *
FROM Invoice
ORDER BY CAST(LEFT(list,CHARINDEX(''-'',list+''-'')-1)AS INT)
,CAST(RIGHT(InvoiceId,CHARINDEX(''-'',REVERSE(InvoiceId)+''-''))AS INT)DESC
Demostración con diferentes longitudes antes del guión: SQL Fiddle
Ordenar por cada parte por separado es la forma más sencilla y confiable de hacerlo, ¿por qué buscar otros enfoques? Echa un vistazo a esta simple consulta.
select *
from Invoice
order by Convert(int, SUBSTRING(invoiceid, 0, CHARINDEX(''-'',invoiceid+''-''))) asc,
Convert(int, SUBSTRING(invoiceid, CHARINDEX(''-'',invoiceid)+1, LEN(invoiceid)-CHARINDEX(''-'',invoiceid))) asc
Divida el género en dos secciones:
Configuración de esquema MS SQL Server 2008 :
CREATE TABLE TestData
(
data varchar(20)
)
INSERT TestData
SELECT ''790711'' as data
UNION
SELECT ''790109-1''
UNION
SELECT ''790109-11''
UNION
SELECT ''790109-2''
Consulta 1 :
SELECT *
FROM TestData
ORDER BY
FLOOR(CAST(REPLACE(data, ''-'', ''.'') AS FLOAT)),
CASE WHEN CHARINDEX(''-'', data) > 0
THEN CAST(RIGHT(data, len(data) - CHARINDEX(''-'', data)) AS INT)
ELSE 0
END
| DATA |
-------------
| 790109-1 |
| 790109-2 |
| 790109-11 |
| 790711 |
Prueba esto
SELECT invoiceid FROM Invoice
ORDER BY
CASE WHEN PatIndex(''%[-]%'',invoiceid) > 0
THEN LEFT(invoiceid,PatIndex(''%[-]%'',invoiceid)-1)
ELSE invoiceid END * 1
,CASE WHEN PatIndex(''%[-]%'',REVERSE(invoiceid)) > 0
THEN RIGHT(invoiceid,PatIndex(''%[-]%'',REVERSE(invoiceid))-1)
ELSE NULL END * 1
Por encima de la consulta utiliza dos declaraciones de casos
- Ordena la primera parte de Invoiceid 790109-1 (p. Ej .: 790709)
- Ordena la segunda parte de Invoiceid después de dividir con ''-'' 790109-1 (p. Ej .: 1)
Para una comprensión detallada, consulte el siguiente SQLfiddle
Demostración detallada SQLFiddle
O use ''CHARINDEX''
SELECT invoiceid FROM Invoice
ORDER BY
CASE WHEN CHARINDEX(''-'', invoiceid) > 0
THEN LEFT(invoiceid, CHARINDEX(''-'', invoiceid)-1)
ELSE invoiceid END * 1
,CASE WHEN CHARINDEX(''-'', REVERSE(invoiceid)) > 0
THEN RIGHT(invoiceid, CHARINDEX(''-'', REVERSE(invoiceid))-1)
ELSE NULL END * 1