Consulta SQL (ordenar por)

votos
1

Quiero enumerar (una lista ordenada) todas mis entradas de un atributo llamado streetNames en mi tabla / relación Clientes. p.ej. Quiero lograr el siguiente orden:

Street_1A
Street_1B
Street_2A
Street_2B
Street_12A
Street_12B

Un pedido simple de streetNames hará una comparación léxica y luego Street_12A y B aparecerán antes que Street_2A / B, y eso no es correcto. ¿Es posible resolver esto por SQL puro?

Publicado el 09/12/2008 a las 16:05
fuente por usuario
En otros idiomas...                            


7 respuestas

votos
2

Para el registro: se llama orden de clasificación natural , y hay un artículo de horror de codificación en el tema.

Supongo que puedes hacerlo en SQL usando parte del código que se muestra aquí, pero siempre lo hará en una situación de caso por caso.

Respondida el 09/12/2008 a las 17:11
fuente por usuario

votos
2

Seleccione street_name del orden tablex por udf_getStreetNumber (street_name)

en tu udf_getStreetNumber: escribe la regla de tu negocio para eliminar el número

EDITAR

Creo que ahora puede usar la funcionalidad regex en SQL Server. Me gustaría quitar todos los caracteres que no sean números de la entrada.

Respondida el 09/12/2008 a las 16:10
fuente por usuario

votos
1

Si tiene acceso de escritura a la base de datos, realmente recomiendo convertirlo todo para usar 3 campos separados y luego usarlos apropiadamente. De esta forma, incluso podrías hacerlo en PHP (sí, llevará algo de tiempo, pero sucederá solo una vez). Esto podría ser algo doloroso si tiene una gran base de código, teniendo que verificar todas las consultas con esta tabla, pero eventualmente se amortizará más tarde. Por ejemplo, hará que la búsqueda por dirección sea mucho más fácil.

Respondida el 09/12/2008 a las 16:27
fuente por usuario

votos
1

Estoy seguro de que podrías dividir el campo streetName en sus diferentes piezas con algo como substr (streetName, 1, find ("", streetName)) solo para la calle, y así sucesivamente. Pero eso va a ser bastante desordenado y tendrá que tratar con todo tipo de casos especiales (sin número de casa, número de casa sin una adición) o asuntos internacionales (en los Estados Unidos, las direcciones son típicamente como 1 calle).

Pero si quiere la clasificación como describió y ese es un requisito importante, sería mejor modelar su streetName en tres partes, es decir, street (por ejemplo, "Street"), house_number (por ejemplo, 1, 2, 12), house_num_addition ( por ejemplo, "A", "B"). Entonces el género se vuelve trivial en SQL.

Respondida el 09/12/2008 a las 16:15
fuente por usuario

votos
-1

Si es el caso, todos los valores en la columna streetNames siguen el patrón StreetName- space - StreetNumber

donde StreetName puede contener otros espacios, pero StreetNumber CAN NOT, entonces esto funcionará:

Declare @T Table (streetName VarChar(50))
Insert @T(streetName) Values('Street 1A')
Insert @T(streetName) Values('Street 2A')
Insert @T(streetName) Values('Street 2B')
Insert @T(streetName) Values('Street 12A')
Insert @T(streetName) Values('Another Street 1A')
Insert @T(streetName) Values('Another Street 4A')
Insert @T(streetName) Values('a third Street 12B')
Insert @T(streetName) Values('a third Street 1C')

Select * From @T 
Order By Substring(StreetName, 0, 1 + len(StreetName) - charIndex(' ', reverse(StreetName))),
       Cast(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)),  
        Case When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 5)) = 1  Then 5
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 4)) = 1  Then 4
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 3)) = 1  Then 3
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 2)) = 1  Then 2
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 1)) = 1  Then 1
                End) as Integer),
        Substring(StreetName, len(StreetName) - charIndex(' ', reverse(StreetName)) +
            Case When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 5)) = 1  Then 5
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 4)) = 1  Then 6
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 3)) = 1  Then 5
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 2)) = 1  Then 4
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 1)) = 1  Then 3
                End, Len(StreetName))
Respondida el 09/12/2008 a las 16:35
fuente por usuario

votos
-1

¡Si es posible! ¡Pero definitivamente sin interés! Si encuentras a alguien aquí listo para pasar unas horas escribiendo y probando el SP que dividirá tus streetNames en una combinación streetName + streetNumber, dame su nombre: le enviaré algunos problemas donde pensé que tenía que pagar para obtener el trabajo hecho.

Por cierto, ¿no puedes dividir tus datos en 2 campos, uno 'streetName' con solo el nombre de la calle, y un nuevo campo 'buildingNumber'? (Evite nombrar este 'streetNumber', ya que, en algunos países / ciudades, las calles reciben números).

Respondida el 09/12/2008 a las 16:13
fuente por usuario

votos
-2

La forma confiable de hacerlo (confiable en términos de "ordenar sus datos correctamente", no "para resolver su problema general") es dividir los datos en el nombre de la calle y el número de la casa y ordenarlos a ambos por su cuenta. Pero esto requiere saber dónde comienza el número de la casa. Y esta es la parte difícil: hacer que la suposición se ajuste mejor a sus datos.

Debería usar algo como lo siguiente para refactorizar sus datos y, a partir de ahora, almacenar el número de la casa en un campo separado. Todo este malabarismo de cuerdas no funcionará demasiado bien cuando se trata de ordenar grandes conjuntos de datos.

Suponiendo que es lo último en el nombre de la calle, y contiene un número:

DECLARE @test TABLE
(
  street VARCHAR(100)
)

INSERT IGNORE  INTO @test (street) VALUES('Street')
INSERT IGNORE  INTO @test (street) VALUES('Street 1A')
INSERT IGNORE  INTO @test (street) VALUES('Street1 12B')
INSERT IGNORE  INTO @test (street) VALUES('Street 22A')
INSERT IGNORE  INTO @test (street) VALUES('Street1 200B-8a')
INSERT IGNORE  INTO @test (street) VALUES('')
INSERT IGNORE  INTO @test (street) VALUES(NULL)

SELECT
  street,
  CASE 
    WHEN LEN(street) > 0 AND CHARINDEX(' ', REVERSE(street)) > 0
    THEN CASE
      WHEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1) LIKE '%[0-9]%'
      THEN LEFT(street, LEN(street) - CHARINDEX(' ', REVERSE(street)))
    END
  END street_part,
  CASE 
    WHEN LEN(street) > 0 AND CHARINDEX(' ', REVERSE(street)) > 0
    THEN CASE 
      WHEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1) LIKE '%[0-9]%'
      THEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1)
    END
  END house_part,
  CASE 
    WHEN LEN(street) > 0 AND CHARINDEX(' ', REVERSE(street)) > 0
    THEN CASE 
      WHEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1) LIKE '%[0-9]%'
      THEN CASE
        WHEN PATINDEX('%[a-z]%', LOWER(RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1))) > 0
        THEN CONVERT(INT, LEFT(RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1), PATINDEX('%[^0-9]%', LOWER(RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1))) - 1))
      END
    END
  END house_part_num
FROM
  @test 
ORDER BY
  street_part,
  house_part_num,
  house_part

Esto asume estas condiciones:

  • una dirección de calle puede tener un número de casa
  • un número de casa debe ser lo último en una calle (no "525 Monroe Av.")
  • un número de casa debe comenzar con un dígito para ser ordenado correctamente
  • un número de casa puede ser un rango ("200-205"), esto se ordenaría por debajo de 200
  • un número de casa no debe contener espacios o el reconocimiento falla (cuando mira sus datos, puede aplicar algo así como REPLACE(street, ' - ', '-')desinfectar patrones comunes de antemano).
  • todo esto sigue siendo una aproximación que ciertamente se desvía de lo que sería en una guía telefónica, por ejemplo
Respondida el 09/12/2008 a las 16:29
fuente por usuario

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more