如何在SQL SERVER 2008中使用滞后功能 [英] How to use LAG FUNCTION in SQL SERVER 2008

问题描述

我正在使用此SQL函数在SQL Server 2012上编写

I`m writing on SQL Server 2012 in this SQL Function

;With Quote as (
   SELECT SID,SHEET,Code, Date, Data, 
          LAG(Data) OVER(ORDER BY Date) As LastMonthData 
   FROM RMQ_DATA WHERE [SHEET] IN(0)
)
SELECT [Quote].[SID], Quote.DATE,Quote.DATA,Quote.SHEET, Quote.CODE,
       CASE 
          WHEN ISNULL(LastMonthData, 0) = 0 THEN null 
          ELSE (LastMonthData/Data) * 100 
       END As Quote,
       RMQ_SUBCAT.TARGET_CODE, RMQ_SUBCAT.RMQ_SUBCAT, RMQ_CAT.RMQ_CAT_NAME, 
       RMQ_CAT.ENABLED
FROM Quote
Left outer Join RMQ_SUBCAT on  Quote.CODE =RMQ_SUBCAT.TARGET_CODE
left outer join RMQ_CAT on RMQ_SUBCAT.TARGET_SID=RMQ_CAT.SID
where RMQ_CAT.ENABLED='Y' and 
      Quote.DATE between '2015/01/01' and '2015/11/01'

但在第一行

With Quote as ( 
   SELECT SID,SHEET,Code, Date, Data, 
          LAG(Data) OVER(ORDER BY DATE)

** LAG(Data)我有一个错误.所以我不知道LAG()函数如何在SQL Server 2008中使用.

**LAG(Data) I have an Error. So I don`t know LAG() function how to use in SQL Server 2008.

任何人都可以帮助我吗?

Anybody Can help me ?

推荐答案

不幸的是，LAG不适用于SQL Server 2012年之前的版本.您必须将ROW_NUMBER与自连接一起使用:

Unfortunately LAG is not available to versions prior to 2012 in SQL Server. You have to use ROW_NUMBER with a self join instead:

;With Quote as (
   SELECT SID, SHEET,Code, [Date], Data,
          ROW_NUMBER() OVER (ORDER BY [Date]) AS rn
   FROM RMQ_DATA 
   WHERE [SHEET] IN (0)
), Quote_LAG AS (
   SELECT q1.SID, q1.SHEET, q1.Code, q1.[Date], q1.Data,
          q2.Data AS LastMonthData
   FROM Quote AS q1
   LEFT JOIN Quote AS q2 ON q1.rn = q2.rn + 1
)
... rest of the query here

这篇关于如何在SQL SERVER 2008中使用滞后功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！