Alias wird bei OrderBy ignoriert.

Question

Wir haben zwei Tabellen in unserer Anwendung, die beide eine ShowOrder-Spalte haben. In unserer Anwendung verwenden wir NHibernate und mit HQL verknüpfen wir diese beiden Tabellen geordnet nach ShowOrder der ersten Tabelle und der zweiten Tabelle.

Hier ist eine vereinfachte Version meiner Abfrage:

SELECT pr.Id as Id,pr.Title as Title, pr.ShowOrder as ShowOrder 
FROM Process pr 
    INNER JOIN ProcessGroup prg ON pr.GroupId=prg.Id 
ORDER BY prg.ShowOrder,pr.ShowOrder

Im Allgemeinen funktioniert unsere Anwendung ohne Probleme. Aber wir haben eine alte Anwendung und eine Konvertierungsroutine, um ihre Datenbank in unsere neue Anwendungsdatenbank zu konvertieren.

Immer wenn wir eine alte Datenbank in unsere neue Datenbank konvertieren, tritt ein Fehler auf, wenn der SQL-Server die obige Abfrage ausführen möchte. Die Ausnahme besagt:

Eine Spalte wurde mehr als einmal in der Order-by-Liste angegeben

Wenn wir pr.ShowOrder ohne Alias auswählen, ist alles in Ordnung.

Es scheint, dass wenn es einen ShowOrder Alias in der Select-Liste gibt, ignoriert der SQL-Server Tabellenaliasse und nimmt an, dass pr.ShowOrder und prg.ShowOrder dasselbe sind.

Answer 1

Dies ist das Standardverhalten von ANSI SQL und wurde von SQL Server mit der Version 2005 übernommen.

ORDER BY arbeitet NICHT mit den Spaltenwerten der Quelltabellen (FROM-Klausel), sondern technisch gesehen NUR mit den Ausgabe-Spaltenwerten in der SELECT-Klausel (**). Wenn du also "ShowOrder" in der ORDER BY-Klausel angibst, verwendet es tatsächlich den Wert von ShowOrder in der Ausgabeliste (was zufällig pr.ShowOrder ist). Wenn du beide ShowOrders verwenden möchtest, solltest du sie beide mit unterschiedlichen Namen in die SELECT-Klausel einfügen.

Der Grund, warum es nicht meckert, wenn du die Quelle Tischnamen verwendest (was technisch gesehen der Fall sein sollte), liegt an der Kompatibilität mit SQL Server 2000, aber das ist eine gefährliche Kompatibilität. Egal, welchen Alias-Namen du in ORDER BY verwendest, tatsächlich verwendet es nur denjenigen, der in der Ausgabeliste erscheint.

(** - und ja, es gibt Möglichkeiten, dies zu umgehen, aber sie dienen hauptsächlich dazu, eine gewisse Kompatibilität mit früheren Versionen zu gewährleisten. Es ist besser, einfach dem Standard zu folgen).

Answer 2

Dies ist eine Verhaltensänderung zwischen SQL Server 2000 und SQL Server 2005

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/198b29db-f223-4aaf-a9f2-965c8a1fa8eb

Answer 3

Ein weiterer Grund für diesen Fehler könnte sein, dass der Kompatibilitätsmodus der Datenbank auf "2000-Modus" festgelegt ist (dies kann passieren, nachdem Sie Ihren SQL Server von 2000 auf 2005 oder höher aktualisiert haben).

Lösung:

"SQL Management Studio" - Rechtsklick auf die Datenbank - "Eigenschaften" - "Optionen" - "Kompatibilitätsgrad" - auf 90 oder höher einstellen.