SQL服务器上的语法错误

I am no SQL expert but I can't see why I'm getting an error with the following code: http://sqlfiddle.com/#!3/2de76/7. Please can someone tell me what is wrong with this. Thanks.

我不是SQL专家，但是我不明白为什么我在下面的代码中会出现错误:http://sqlfiddle.com/#!谁能告诉我这是怎么回事吗?谢谢。

DECLARE @total INT
SET @total = 0
SELECT
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN @total = @total
        WHEN s1.dayTotal > s2.dayTotal      THEN @total = @total + (s1.dayTotal - s2.dayTotal)
    END     AS res,
    s1.dayTotal,
    s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
ORDER BY s1.RowCountID ASC

The expected output:

预期的输出:

rowCount,res,dayTotal,dayTotal
1,3,3,0
2,4,4,3
3,4,3,4
4,7,6,3

3 个解决方案

#1

You're trying to use the CASE's THEN to alter the value of a variable. Doesn't work like that. This, however, works, and looks more close to what you might want to achieve:

你试图用这个例子来改变变量的值。不工作。然而，这是可行的，而且看起来更接近你想要达到的目标:

DECLARE @total INT
SET @total = 0
SELECT 
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN @total
        WHEN s1.dayTotal > s2.dayTotal      THEN (s1.dayTotal - s2.dayTotal)
    END     AS res,
    s1.dayTotal,
    s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
ORDER BY s1.RowCountID ASC

PS - your CASE doesn't take the possibility of s1.dayTotal = s2.dayTotal into account. Not sure that's okay.

PS -你的案子没有s1的可能性。dayTotal = s2。dayTotal考虑在内。不知道没关系。

PPS - if you want the second WHEN to sum up the total AND the difference, use

如果你想要第二个什么时候合计总数和差额，使用

WHEN s1.dayTotal > s2.dayTotal      THEN @total+(s1.dayTotal - s2.dayTotal)

PPPS - This seems to produce the desired resultset:

PPPS -这似乎产生了期望的结果集:

select a.RowCountID, case when b.diff > 0 then a.res+b.diff else a.res end as res, a.daytotal1, a.daytotal2  from
(
SELECT 
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN s2.dayTotal
        WHEN s1.dayTotal >= s2.dayTotal     THEN s1.dayTotal
    END     AS res,
    s1.dayTotal as dayTotal1,
    s2.dayTotal as dayTotal2,
    s2.dayTotal - s1.dayTotal as diff
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
  ) a 
  left join
(
  SELECT 
    s1.RowCountID,
    CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN s2.dayTotal
        WHEN s1.dayTotal >= s2.dayTotal     THEN s1.dayTotal
    END     AS res,
    s1.dayTotal as dayTotal1,
    s2.dayTotal as dayTotal2,
    s2.dayTotal - s1.dayTotal as diff
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
) b on a.rowcountID = b.rowcountID+1
order by a.rowcountID

Not sure what this kind of self-joining query is named, but it should have a name :)

不确定这种自连接查询的名称，但它应该有一个名称:)

Hope it works well!

希望它工作得很好!

#2

You need to remove "@total =" from the case statement.

您需要从case语句中删除“@total =”。

DECLARE @total int
SET @total = 0

SELECT
  s1.RowCountID,
  CASE
    WHEN s1.dayTotal < s2.dayTotal THEN @total
    WHEN s1.dayTotal > s2.dayTotal THEN @total + (s1.dayTotal - s2.dayTotal)
  END AS res,
  s1.dayTotal,
  s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2
  ON s2.RowCountID = (s1.RowCountID - 1)
ORDER BY s1.RowCountID ASC

#3

Ok after further investigation (and finding how to get cumulative sum), the following is the correct code to make it work (but with the addition of the case command):

好的，在进一步的调查(以及查找如何获得累积和)之后，以下是使其工作的正确代码(但是添加case命令):

SELECT
    s1.RowCountID,
      SUM(
      CASE
        WHEN s1.dayTotal < s2.dayTotal      THEN 0
        WHEN s1.dayTotal > s2.dayTotal      THEN s1.dayTotal - s2.dayTotal
      END) OVER(
           ORDER BY s1.RowCountID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS res2,
    s1.dayTotal,
    s2.dayTotal
FROM simple_summary s1
JOIN simple_summary s2 ON s2.RowCountID = s1.RowCountID - 1
ORDER BY s1.RowCountID ASC

My actual issue is I also use MySQL (I was experimenting to see if it was possible elsewhere) so the next question is how this would look in MySQL?? That will take some further investigations!!

我的实际问题是我也使用MySQL(我在试验是否可能在其他地方使用)，所以下一个问题是这在MySQL中会是什么样子?这需要进一步的调查!!

#1