我有以下查询:
WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY TimeStamp) AS rn
FROM [AVL_Ignition]
)
SELECT mc.[DeviceIMEI], (mp.TimeStamp - mc.TimeStamp) as millisecond, mc.Value, mc.Tag
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1
此查询工作正常,并返回以下值
DeviceIMEI| milissecond|value
123 | 184 |1
123 | 184 |0
123 | 184 |1
123 | 184 |0
123 | 184 |1
123 | 184 |0
我想在毫秒字段中添加值,其中值 = 1
我正在尝试按如下方式使用SELECT SUM,但没有得到结果
SELECT mc.[DeviceIMEI], SUM (WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY TimeStamp) AS rn
FROM [AVL_Ignition]
)
SELECT (mp.TimeStamp - mc.TimeStamp) as millisecond
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1) where mc.Value = 1;
我知道 SELECT SUM 使用起来并不复杂,但我在这个查询中遇到问题
在这种情况下,我会不使用SUM
来做到这一点。如果您使用的是SQL Server 2012+,则可以像这样使用IIF
函数(只需将WITH
部分保留在原处(:
SELECT
mc.DeviceIMEI
, (mp.TimeStamp - mc.TimeStamp) + IIF(mc.Value = 1, 1, 0) AS millisecond
, mc.Value
, mc.Tag
否则,您必须使用CASE
:
SELECT
mc.DeviceIMEI
, (mp.TimeStamp - mc.TimeStamp) + CASE
WHEN mc.Value = 1 THEN mc.Value
ELSE 0
END AS millisecond
, mc.Value
, mc.Tag