wake-up-neo.com

SQL Row_Number () -Funktion in Where-Klausel

Ich habe eine Frage gefunden, die mit der Funktion Row_Number() in der where-Klausel beantwortet wurde. Als ich eine Abfrage ausprobierte, wurde der folgende Fehler angezeigt:

"Nachricht 4108, Ebene 15, Status 1, Zeile 1 Fensterfunktionen können nur in den Klauseln SELECT oder ORDER BY angezeigt werden."

Hier ist die Abfrage, die ich versucht habe. Wenn jemand weiß, wie man das löst, lass es mich wissen.

SELECT employee_id 
FROM V_EMPLOYEE 
WHERE row_number() OVER ( ORDER BY employee_id ) > 0 
ORDER BY Employee_ID
sqlsql-servertsqlanalytic-functions
81
Joseph

Um dieses Problem zu umgehen, schließen Sie Ihre select-Anweisung in einen CTE ein. Anschließend können Sie eine Abfrage für den CTE durchführen und die Ergebnisse der Fensterfunktion in der where-Klausel verwenden.

WITH MyCte AS 
(
    select   employee_id,
             RowNum = row_number() OVER ( order by employee_id )
    from     V_EMPLOYEE 
    ORDER BY Employee_ID
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0
81
Scott Ivey
SELECT  employee_id
FROM    (
        SELECT  employee_id, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
        FROM    V_EMPLOYEE
        ) q
WHERE   rn > 0
ORDER BY
        Employee_ID

Beachten Sie, dass dieser Filter redundant ist: ROW_NUMBER() beginnt mit 1 Und ist immer größer als 0.

56
Quassnoi
Select * from 
(
    Select ROW_NUMBER() OVER ( order by Id) as 'Row_Number', * 
    from tbl_Contact_Us
) as tbl
Where tbl.Row_Number = 5
28
swa

Ich denke du willst so etwas:

SELECT employee_id 
FROM  (SELECT employee_id, row_number() 
       OVER (order by employee_id) AS 'rownumber' 
       FROM V_EMPLOYEE) TableExpressionsMustHaveAnAliasForDumbReasons
WHERE rownumber > 0
19
Matthew Jones

Als Antwort auf Kommentare zur Antwort von rexem, ob eine Inline-Ansicht oder ein CTE schneller wäre, habe ich die Abfragen neu gegossen, um eine Tabelle zu verwenden, die ich und alle anderen zur Verfügung hatten: sys.objects.

WITH object_rows AS (
    SELECT object_id, 
        ROW_NUMBER() OVER ( ORDER BY object_id) RN
    FROM sys.objects)
SELECT object_id
FROM object_rows
WHERE RN > 1

SELECT object_id
FROM (SELECT object_id, 
        ROW_NUMBER() OVER ( ORDER BY object_id) RN
    FROM sys.objects) T
WHERE RN > 1

Die erstellten Abfragepläne waren exakt gleich. Ich würde in jedem Fall erwarten, dass das Abfrageoptimierungsprogramm denselben Plan erstellt, zumindest beim einfachen Ersetzen des CTE mit Inline-Ansicht oder umgekehrt.

Probieren Sie natürlich Ihre eigenen Abfragen auf Ihrem eigenen System aus, um festzustellen, ob es einen Unterschied gibt.

Außerdem ist row_number() in der where-Klausel ein häufiger Fehler bei Antworten auf Stack Overflow. Logisch row_number() ist erst verfügbar, wenn die select-Klausel verarbeitet wurde. Die Leute vergessen das und wenn sie antworten, ohne die Antwort zu testen, ist die Antwort manchmal falsch. (Eine Anklage, für die ich mich schuldig gemacht habe.)

7
Shannon Severance

Verwenden von CTE (SQL Server 2005+):

WITH employee_rows AS (
  SELECT t.employee_id,
         ROW_NUMBER() OVER ( ORDER BY t.employee_id ) 'rownum'
    FROM V_EMPLOYEE t)
SELECT er.employee_id
  FROM employee_rows er
 WHERE er.rownum > 1

Verwenden der Inline-Ansicht/Alternative ohne CTE-Äquivalent:

SELECT er.employee_id
  FROM (SELECT t.employee_id,
               ROW_NUMBER() OVER ( ORDER BY t.employee_id ) 'rownum'
          FROM V_EMPLOYEE t) er
 WHERE er.rownum > 1
4
OMG Ponies

basierend auf der Antwort von OP auf die Frage:

Bitte sehen Sie diesen Link. Es hat eine andere Lösung, die für die Person, die die Frage gestellt hat, funktioniert. Ich versuche, eine Lösung wie diese zu finden.

Paginierte Abfrage mithilfe der Sortierung in verschiedenen Spalten mithilfe von ROW_NUMBER () OVER () in SQL Server 2005

~ Joseph

"Methode 1" entspricht der Abfrage des OP aus der verknüpften Frage, und "Methode 2" entspricht der Abfrage aus der ausgewählten Antwort. Sie mussten sich den Code ansehen, der in diesem Antwort verlinkt ist, um zu sehen, was wirklich vor sich geht, da der Code in der ausgewählten Antwort geändert wurde, damit es funktioniert. Versuche dies:

DECLARE @YourTable table (RowID int not null primary key identity, Value1 int, Value2 int, value3 int)
SET NOCOUNT ON
INSERT INTO @YourTable VALUES (1,1,1)
INSERT INTO @YourTable VALUES (1,1,2)
INSERT INTO @YourTable VALUES (1,1,3)
INSERT INTO @YourTable VALUES (1,2,1)
INSERT INTO @YourTable VALUES (1,2,2)
INSERT INTO @YourTable VALUES (1,2,3)
INSERT INTO @YourTable VALUES (1,3,1)
INSERT INTO @YourTable VALUES (1,3,2)
INSERT INTO @YourTable VALUES (1,3,3)
INSERT INTO @YourTable VALUES (2,1,1)
INSERT INTO @YourTable VALUES (2,1,2)
INSERT INTO @YourTable VALUES (2,1,3)
INSERT INTO @YourTable VALUES (2,2,1)
INSERT INTO @YourTable VALUES (2,2,2)
INSERT INTO @YourTable VALUES (2,2,3)
INSERT INTO @YourTable VALUES (2,3,1)
INSERT INTO @YourTable VALUES (2,3,2)
INSERT INTO @YourTable VALUES (2,3,3)
INSERT INTO @YourTable VALUES (3,1,1)
INSERT INTO @YourTable VALUES (3,1,2)
INSERT INTO @YourTable VALUES (3,1,3)
INSERT INTO @YourTable VALUES (3,2,1)
INSERT INTO @YourTable VALUES (3,2,2)
INSERT INTO @YourTable VALUES (3,2,3)
INSERT INTO @YourTable VALUES (3,3,1)
INSERT INTO @YourTable VALUES (3,3,2)
INSERT INTO @YourTable VALUES (3,3,3)
SET NOCOUNT OFF

DECLARE @PageNumber     int
DECLARE @PageSize       int
DECLARE @SortBy         int

SET @PageNumber=3
SET @PageSize=5
SET @SortBy=1


--SELECT * FROM @YourTable

--Method 1
;WITH PaginatedYourTable AS (
SELECT
    RowID,Value1,Value2,Value3
        ,CASE @SortBy
             WHEN  1 THEN ROW_NUMBER() OVER (ORDER BY Value1 ASC)
             WHEN  2 THEN ROW_NUMBER() OVER (ORDER BY Value2 ASC)
             WHEN  3 THEN ROW_NUMBER() OVER (ORDER BY Value3 ASC)
             WHEN -1 THEN ROW_NUMBER() OVER (ORDER BY Value1 DESC)
             WHEN -2 THEN ROW_NUMBER() OVER (ORDER BY Value2 DESC)
             WHEN -3 THEN ROW_NUMBER() OVER (ORDER BY Value3 DESC)
         END AS RowNumber
    FROM @YourTable
    --WHERE
)
SELECT
    RowID,Value1,Value2,Value3,RowNumber
        ,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
    FROM PaginatedYourTable
    WHERE RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
    ORDER BY RowNumber



--------------------------------------------
--Method 2
;WITH PaginatedYourTable AS (
SELECT
    RowID,Value1,Value2,Value3
        ,ROW_NUMBER() OVER
         (
             ORDER BY
                 CASE @SortBy
                     WHEN  1 THEN Value1
                     WHEN  2 THEN Value2
                     WHEN  3 THEN Value3
                 END ASC
                ,CASE @SortBy
                     WHEN -1 THEN Value1
                     WHEN -2 THEN Value2
                     WHEN -3 THEN Value3
                 END DESC
         ) RowNumber
    FROM @YourTable
    --WHERE  more conditions here
)
SELECT
    RowID,Value1,Value2,Value3,RowNumber
        ,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
    FROM PaginatedYourTable
    WHERE 
        RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
        --AND more conditions here
    ORDER BY
        CASE @SortBy
            WHEN  1 THEN Value1
            WHEN  2 THEN Value2
            WHEN  3 THEN Value3
        END ASC
       ,CASE @SortBy
            WHEN -1 THEN Value1
            WHEN -2 THEN Value2
            WHEN -3 THEN Value3
        END DESC

AUSGABE:

RowID  Value1 Value2 Value3 RowNumber  PageNumber  PageSize    SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10     2      1      1      10         3           5           1
11     2      1      2      11         3           5           1
12     2      1      3      12         3           5           1
13     2      2      1      13         3           5           1
14     2      2      2      14         3           5           1

(5 row(s) affected

RowID  Value1 Value2 Value3 RowNumber  PageNumber  PageSize    SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10     2      1      1      10         3           5           1
11     2      1      2      11         3           5           1
12     2      1      3      12         3           5           1
13     2      2      1      13         3           5           1
14     2      2      2      14         3           5           1

(5 row(s) affected)
2
KM.
WITH MyCte AS 
(
    select 
       employee_id,
       RowNum = row_number() OVER (order by employee_id)
    from V_EMPLOYEE 
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0
ORDER BY employee_id
2
sumit

Ich bin der Meinung, dass alle Antworten, die die Verwendung eines CTE oder einer Unterabfrage zeigen, ausreichend sind, um dies zu beheben, aber ich sehe niemanden, der den Grund für das Problem von OP herausfindet. Der Grund, warum das von OP vorgeschlagene nicht funktioniert, liegt in der logischen Reihenfolge der Abfrageverarbeitung:

  1. VON
  2. AUF
  3. BEITRETEN
  4. WOHER
  5. GRUPPIERE NACH
  6. MIT WÜRFEL/ROLLUP
  7. HABEN
  8. WÄHLEN
  9. AUSGEZEICHNET
  10. SORTIEREN NACH
  11. OBEN
  12. OFFSET/FETCH

Ich glaube, dass dies einen großen Beitrag zur Beantwortung leistet, denn es erklärt, warum Probleme wie dieses auftreten. WHERE wird immer verarbeitet, bevor SELECT einen CTE oder eine Unterabfrage für viele Funktionen erforderlich macht. Sie werden dies häufig in SQL Server sehen.

0
Jamie Marshall