T-SQL ORDER BY gemäß einer Bedingung

Question

Ich schreibe gerade ein Ressourcen-Management-System.

Ein Ressource ist eine Instanz einer Definition. Eine Definition ist im Grunde genommen die Metadaten, sie enthält die Eigenschaften.

Das ist im Allgemeinen meine Datenbank:

TypeDefinition
id    name
===============
1     CPU

PropertyDefinition
id    name       typeDefinitionId   valueType
================================================
1     frequency  1                  int
2     status     1                  string

TypeInstance
id    name     typeDefinitionId
=================================
1     CPU#1    1  
2     CPU#2    1

PropertyInstanceValue
id   propertyDefinitionId  typeInstanceId   valueType   intValue  StringValue FloatValue
========================================================================================
1    1                     1                int         10
2    2                     1                string                Pending
3    1                     2                int         20
4    2                     2                string                Approved

ANFORDERUNG:

Ordnen Sie alle Ressourcen nach einem spezifischen Eigenschaftswert.

Zum Beispiel: Ordnen Sie alle Ressourcen nach ihrem Status --> Das bedeutet, dass CPU#2 vor CPU#1 erscheinen wird, weil "Approved" vor "Pending" kommt.

Wenn wir nach Frequenz ordnen würden, wird CPU#1 vor CPU#2 erscheinen, da 10 vor 20 kommt.

Also muss ich jedes Mal nach einer anderen Spalte sortieren (intValue / stringValue/ FloatValue / usw.), je nach dem Wertetyp der Eigenschaft.

Irgendwelche Vorschläge?

BESCHRÄNKUNG:

PIVOT ist derzeit die einzige Option, die wir in Betracht gezogen haben, aber es ist nicht wirklich möglich, da die Datenbank riesig ist und ich möchte, dass die Abfrage so schnell wie möglich ist.

Vielen Dank im Voraus,

Michal.

Answer 1

Wenn das Problem darin besteht, dass Sie die Abfrage nicht dynamisch erstellen möchten, verwenden Sie diese order by Struktur:

order by case @orderby
    when 'status' then status
    when 'frequency' then frequency
    end
option (recompile)

Sie werden den Parameter @orderby übergeben. Die endgültige recompile option zwingt den Motor dazu, einen neuen Plan gemäß den übergebenen Parametern zu erstellen, vorausgesetzt, Sie verwenden eine gespeicherte Prozedur.

Answer 2

Wenn Sie Ihre Abfrageergebnisse mit SQL bestellen möchten (anstatt sie in der Aufrufanwendung nach der Rückgabe der Ergebnisse zu sortieren), müssten Sie Dynamic SQL generieren und es mit sp_executesql ausführen.

http://msdn.microsoft.com/de-de/library/ms188001.aspx

Answer 3

Wenn ich Ihre Frage richtig verstehe, würde ich wie folgt vorgehen:

1. Erstellen Sie eine Tabelle mit erlaubten Zeichenfolgenwerten und achten Sie darauf, eine Spalte für die Sortierreihenfolge anzugeben (nennen wir diese AllowedValues)

   create table [dbo].[AllowedStringValues] ( PropertyDefinitionId int, stringValue varchar(250), sortOrder int)

2. Erstellen Sie eine komplexe Unterabfrage, die den richtigen Wert basierend auf der PropertyDefinition der Zeile auswählt (es scheint, als müsste sie unter 3 Spalten suchen, je nach Datentyp).

3. Wenn der Wert vom Typ String ist, verknüpfen Sie die Unterabfrage mit der Tabelle AllowedStringValues (vorausgesetzt, der Wert wurde in eine Spalten gespeichert)

   inner join AllowedStringValues on ValueType=String AND value = stringValue OR ValueType <> string

4. Sortieren Sie nach der Sortierreihenfolge in AllowedValues, wenn es sich um einen String handelt, oder nach dem numerischen Wert in anderen Fällen.

   order by case ValueType when 'string' then sortOrder else value end