Ordnungsgemäße Sortierung von punktierten Zahlen, die als Zeichen in SQL Server gespeichert sind

Question

Ich habe eine SQL-Tabelle, die eine benutzerdefinierte Positionsnummer speichert. Jede dieser Nummern kann eine untergeordnete Nummer haben, die mit einem Trennzeichen von . . Jeder von ihnen kann auch ein Kind haben.

Ein Beispiel dafür, wie es aussehen könnte (wiederum dynamisch, ich weiß nicht, wie es aussehen wird):

Item Number
1
1.1
1.1.1
1.1.1.1
1.1.1.1.a
1.1.1.1.b
10
11
2.1
2.10
2.2
2.20
20
3
30

Das Schwierige daran ist, dass diese Zahlen spontan und nicht unbedingt in der richtigen Reihenfolge erstellt werden. Sie können 5 Zahlen erstellen (1, 2, 3, 4, 5) und dann ein Kind von 1 erstellen, so dass es nicht in der Reihenfolge in der Datenbank gespeichert wird.

Wie wähle ich aus der Tabelle aus und ordne nach dem Item Number so dass es richtig angezeigt wird, wie oben, wenn die Daten nicht in dieser Reihenfolge gespeichert sind?

Die meisten Lösungen, die ich ausprobiert habe, geben mir entweder 1, 2, 3, 4, 5...1.1, 1.2 OR 1, 1.1, 1.1.1, 10, 11...2, 2.1, 20....3, 30, etc .

Answer 1

Wenn Sie SQL 2008 haben, können Sie den neuen Datentyp hierarchyid verwenden:

WITH Items (ItemNumber) AS (
    SELECT '1' UNION ALL SELECT '1.1' UNION ALL SELECT '1.1.1'
    UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '2'
    UNION ALL SELECT '2.1' UNION ALL SELECT '20' UNION ALL SELECT '3'
    UNION ALL SELECT '30'
)
SELECT *
FROM Items 
ORDER BY Convert(hierarchyid, '/' + ItemNumber + '/');

Answer 2

Ich habe dies in einem anderen Forum erörtert, in dem wir eine XML-Lösung gefunden haben, die sehr dynamisch war. Adam Haines half bei der Optimierung, wodurch sich die Leistung drastisch verbesserte. Diese Version enthält eine Korrektur, um alphabetische Ziffern korrekt zu sortieren.

Bei den folgenden Werten:

declare @temp table (id varchar(255))

insert into @temp (id) values
  ('1.1.a.1'),('1.1.aa.2'),
  ('1.1.b.3'),('1.1.a.4'),
  ('1.1.a.5'),('1.1.a.6'),
  ('1.1.a.7'),('1.1.a.8'),
  ('1.1.a.9'),('1.1.a.10'),
  ('1.1.a.11'),('1.1.b.1'),
  ('1.1.b.2'),('1.2.a.1'),
  ('1.10.a.1'),('1.11.a.1'),
  ('1.20.a.1'),('101.20.a.2'),
  ('1.20.a.150'),('1.1'),
  ('1.2'),('1')

Diese Abfrage:

declare @xml xml,
        @max_len int

set @xml =
(
select id as id, cast('<i>' + replace(id,'.','</i><i>') + '</i>' as xml)
from @temp
for xml path('id_root'),type
)

select @max_len = max(len(x.i.value('.','varchar(10)')))
from @xml.nodes('/id_root/i') x(i)

select [id]--, srt.srtvalue
from @temp
cross apply(
    select case when ISNUMERIC(x.i.value('.','varchar(10)')) = 1 then right(replicate('0',@max_len) + x.i.value('.','varchar(10)'),@max_len) else x.i.value('.','varchar(10)') end + '.'
    from @xml.nodes('/id_root/i') x(i)
    where x.i.value('../id[1]','varchar(50)') = [@temp].id
    for xml path('')
) as srt(srtvalue)
order by srt.srtvalue

Gibt diese Werte zurück:

id
1
1.1
1.1.a.1
1.1.a.4
1.1.a.5
1.1.a.6
1.1.a.7
1.1.a.8
1.1.a.9
1.1.a.10
1.1.a.11
1.1.aa.2
1.1.b.1
1.1.b.2
1.1.b.3
1.2
1.2.a.1
1.10.a.1
1.11.a.1
1.20.a.1
1.20.a.150
101.20.a.2

Wenn Sie mehr als 10 Zeichen in einer einzelnen Ziffer haben, müssen Sie die varchar(10) entsprechend ändern.

-- James

Answer 3

Wenn Sie Dinge numerisch sortieren wollen, speichern Sie sie nicht als nvarchar.

En TATSÄCHLICH Die Lösung besteht darin, diese Zahlen zu ihren eigenen zu machen int Felder für z.B. Version , Versiona , Versionb ...

Entonces ORDER BY Version, Versiona, Versionb

Wenn Sie Zahlen als Zeichen speichern, dürfen Sie nicht erwarten, dass sie wie Zahlen funktionieren.

Answer 4

Dies ist mehr ein Scherz als eine echte Antwort. Wenn

• Ihre Kategorien haben maximal 4 Stufen
• Sie sich wirklich nicht für Leistung interessieren

dann versuchen Sie dies:

WITH Items (ItemNumber) AS (
              SELECT '1' UNION ALL SELECT '1.1' UNION ALL SELECT '1.1.1'
    UNION ALL SELECT '-1' UNION ALL SELECT '1.-1' UNION ALL SELECT '1.-1.1'
    UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '2'
    UNION ALL SELECT '1.2000' UNION ALL SELECT '1.-2000' UNION ALL SELECT '2.1'
    UNION ALL SELECT '2.2' UNION ALL SELECT '20' UNION ALL SELECT '3'
    UNION ALL SELECT '30' UNION ALL SELECT '30.1' UNION ALL SELECT '10.10'
    UNION ALL SELECT '1.-10' UNION ALL SELECT '1.1.1.1'
)

SELECT ItemNumber
FROM 
  ( SELECT
          ItemNumber
        , CASE WHEN ItemNumber LIKE '%.%.%.%' THEN ItemNumber
               WHEN ItemNumber LIKE '%.%.%' THEN ItemNumber + '.0'
               WHEN ItemNumber LIKE '%.%' THEN ItemNumber + '.0.0'
               ELSE ItemNumber + '.0.0.0'
          END AS ItemNumberToParse
    FROM Items
  ) AS tmp
ORDER BY CAST(PARSENAME(ItemNumberToParse, 4) AS INT),
         CAST(PARSENAME(ItemNumberToParse, 3) AS INT),
         CAST(PARSENAME(ItemNumberToParse, 2) AS INT),
         CAST(PARSENAME(ItemNumberToParse, 1) AS INT) ;

Ergebnis:

  ItemNumber
    -1
    1.-2000
    1.-10
    1.-1
    1.-1.1
    1
    1.1
    1.1.1
    1.1.1.1
    1.2000
    2
    2.1
    2.2
    3
    10
    10.10
    11
    20
    30
    30.1

Answer 5

Einige Fragen:

• Wie viele Unterkategorien kann es geben?
• Werden es immer nur Zahlen sein oder können es auch Buchstaben sein?
• Was ist die größte Zahl, die jemals ein einzelner Wert zwischen Punkten sein könnte?

Wenn Sie SQL 2008 verwenden, empfehle ich die Antwort von @Vito, da dies bei weitem die beste Lösung ist.

Wenn Sie eine frühere Version verwenden, müssen Sie etwas nacharbeiten.

Hier ist eine SQL 2005-Version. Ich habe angenommen, dass die Antworten auf die obigen Fragen 100, immer nur Zahlen, und 9999999999 (10 Ziffern) sind.

WITH Items (ItemNumber) AS (
    SELECT '1' UNION ALL SELECT '1.1' UNION ALL SELECT '1.1.1'
    UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '2'
    UNION ALL SELECT '2.1' UNION ALL SELECT '20' UNION ALL SELECT '3'
    UNION ALL SELECT '30' UNION ALL SELECT '9999999999.9999999999'
), Padded AS (
   SELECT
      ItemNumber,
      Convert(nvarchar(max), '') SortValue,
      ItemNumber Remainder,
      0 Selector
   FROM Items
   UNION ALL
   SELECT
      ItemNumber,
      SortValue + Right('000000000' + Left(Remainder, CharIndex('.', Remainder + '.') - 1), 10),
      Substring(Remainder, CharIndex('.', Remainder + '.') + 1, 2147483647),
      CASE WHEN Remainder LIKE '%.%' THEN 0 ELSE 1 END
   FROM Padded
   WHERE
      Remainder <> ''
)
SELECT ItemNumber
FROM Padded
WHERE Selector = 1
ORDER BY SortValue;

Für SQL 2000 wird es ein wenig schwieriger...