我怎样才能使用OVER和ORDER BY

I can't understand this code's bug.

我无法理解这段代码的错误。

CODE:

SELECT
    CariID, HesapID, BTrh, BCinsiID, BNo, Acklm, VdTrh, mTrh, BorcT, AlacakT, 
    SUM(BorcT) OVER (PARTITION BY CariID, HesapID ORDER BY BTrh, BNo, mTrh) AS TopBorcT, 
    SUM(AlacakT) OVER (PARTITION BY CariID, HesapID ORDER BY BTrh, BNo, mTrh ) AS TopAlacakT
FROM
    tCariH

ERROR:

Msg 102, Level 15, State 1, Line 3

Msg 102,Level 15,State 1,Line 3

Incorrect syntax near 'order'.

'order'附近的语法不正确。

3 个解决方案

#1

With an aggregate function like SUM, you don't use ORDER BY in the OVER clause - you only need to use the PARTITION function. The ORDER is used for ranking functions:

使用像SUM这样的聚合函数,在OVER子句中不使用ORDER BY - 您只需要使用PARTITION函数。 ORDER用于排名功能:

Depending on the ranking, aggregate, or analytic function used with the OVER clause, <ORDER BY clause> and/or the <ROWS and RANGE clause> may not be supported.

根据与OVER子句一起使用的排名,聚合或分析函数,可能不支持和/或。和range子句>

Just modify to remove the ORDER in both your aggregates and you should be fine.

只需修改以删除聚合中的ORDER,您就可以了。

#2

Apparently this is not supported in SQL Server 2008, but it is support in SQL Server 2012

显然,SQL Server 2008不支持此功能,但SQL Server 2012支持此功能

How to use partition by and order by in over function?

如何在over函数中使用分区和顺序?

Try this tool and you can see why: http://sqlfiddle.com/#!6/5303f/1

试试这个工具你就会明白为什么:http://sqlfiddle.com/#!6/5303f/1

#3

ID      AccountID       Quantity
1          1               10           Sum = 10
2          1               5                = 10 + 5 = 15
3          1               2                = 10 + 5 + 2 = 17
4          2               7                = 7
5          2               3                = 7 + 3 = 10  

SELECT ID, AccountID, Quantity, 
       SUM(Quantity) OVER (PARTITION BY AccountID ) AS TopBorcT, 
FROM tCariH

#1