我有下表:
ID GROUPID ODATE OTIME OVALUE
1 A 2014-05-31 00:00:00 1207432.6
2 A 2014-05-31 01:00:00 1209064
3 A 2014-05-31 02:00:00 1210698
4 A 2014-05-31 03:00:00 1212333.3
5 A 2014-05-31 04:00:00 1213967.7
6 B 2014-05-31 00:00:00 2110016
7 B 2014-05-31 01:00:00 2110016
8 B 2014-05-31 02:00:00 2110016
9 B 2014-05-31 03:00:00 2110016
10 B 2014-05-31 04:00:00 2110016
11 C 2014-05-31 00:00:00 2326592.6
12 C 2014-05-31 01:00:00 2328088.8
13 C 2014-05-31 02:00:00 2329590.3
14 C 2014-05-31 03:00:00 2331094.5
15 C 2014-05-31 04:00:00 2332598
然后我运行以下语法:
SELECT
A.ID, A.GroupID, A.oDate, A.oTime,
A.oValue, MAX(B.oValue) AS Prev_oValue, A.oValue - MAX(B.oValue) AS oResult
FROM
Table1 AS A LEFT OUTER JOIN Table1 AS B ON B.GroupID = A.GroupID AND B.oValue < A.oValue
GROUP BY
A.ID, A.GroupID, A.oDate, A.oTime, A.oValue
ORDER BY A.GroupID, A.oDate, A.oTime
我想得到以下结果:
ID GROUPID ODATE OTIME OVALUE PREV_OVALUE ORESULT
1 A 2014-05-31 00:00:00 1207432.6 (null) (null)
2 A 2014-05-31 01:00:00 1209064 1207432.6 1631.4
3 A 2014-05-31 02:00:00 1210698 1209064 1634
4 A 2014-05-31 03:00:00 1212333.3 1210698 1635.3
5 A 2014-05-31 04:00:00 1213967.7 1212333.3 1634.4
6 B 2014-05-31 00:00:00 2110016 (null) (null)
7 B 2014-05-31 01:00:00 2110016 2110016 0
8 B 2014-05-31 02:00:00 2110016 2110016 0
9 B 2014-05-31 03:00:00 2110016 2110016 0
10 B 2014-05-31 04:00:00 2110016 2110016 0
11 C 2014-05-31 00:00:00 2326592.6 (null) (null)
12 C 2014-05-31 01:00:00 2328088.8 2326592.6 1496.2
13 C 2014-05-31 02:00:00 2329590.3 2328088.8 1501.5
14 C 2014-05-31 03:00:00 2331094.5 2329590.3 1504.2
15 C 2014-05-31 04:00:00 2332598 2331094.5 1503.5
检查小提琴
我想要的是,根据GroupID列和“按日期和时间排序”列获取以前的值。得到前一个值后,当前记录减去前一个值AS RESULT。但是出了点问题,结果很糟糕。一些记录获取先前的值,而某些记录则没有。我听不懂
有谁知道如何实现这一目标?
谢谢你。
您可以使用带有ROW_NUMBER()的公用表表达式来按时间顺序对组中的每一行进行编号。这样,获得前一个值就很简单,只需将cte与自身连接起来即可获得具有相同groupid且行号少一的行。就像是;
WITH cte AS (
SELECT groupid, odate, otime, ovalue,
ROW_NUMBER() OVER (PARTITION BY groupid ORDER BY odate, otime) rn
FROM table1
)
SELECT a.groupid, a.odate, a.otime, a.ovalue, b.ovalue Prev_oValue,
a.ovalue-b.ovalue oResult
FROM cte a
LEFT JOIN cte b
ON a.groupid = b.groupid
AND a.rn = b.rn + 1
ORDER BY a.groupid, a.odate, a.otime
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句