SQL中的开始和结束语法错误If语句(SQL Server 2012)

Question

我被要求创建一个分数，从当前一周的比较到过去四个星期的平均值。( Note 2018年2/2/2018，因为我拥有的测试数据是陈旧的(

代码工作正常，直到我不得不围绕我的语句执行来结束并结束以添加以更新评分表。我开始收到错误通知，然后又有其他内容。SQL Server 2012仅响应"语法错误"。我已经四处搜索了代码示例，但是我发现的所有节目都按照我粘贴的方式进行。在这可能导致的事情的这一点上，我陷入了困境。谁能提供帮助？

Declare @HighScore as float(2) 
Set @HighScore = 35
IF (Select COUNT (Date) FROM data WHERE DATEDIFF(d,Date,'2/2/2018') <= 6 AND Category='High')
>
(Select COUNT (Date)/4 FROM data WHERE DATEDIFF(d,Date,'2/2/2018')>=7 AND DATEDIFF(d,Date,'2/2/2018')<35 AND Category='High')
BEGIN
    SET @HighScore = @HighScore - 5 
    INSERT INTO Scoring ('2/2/2018', 'High', @HIGHSCORE, 1)
END
ELSE 
    (Select COUNT (Date) FROM data WHERE DATEDIFF(d,Date,'2/2/2018')<=6 AND Category='High')
<
(Select COUNT (Date)/4 FROM data WHERE DATEDIFF(d,Date,'2/2/2018')>=7 AND DATEDIFF(d,Date,'2/2/2018')<35 AND Category='High')
BEGIN
    SET @HighScore = @HighScore + 5
    INSERT INTO Scoring ('2/2/2018', 'High', @HIGHSCORE, 1)
END
ELSE 
BEGIN
    SET @HighScore = @HighScore + 0
    INSERT INTO Scoring ('2/2/2018', 'High', @HIGHSCORE, 1)
END

Answer 1

您在第一个ELSE之后缺少IF(因为您正在进行另一个比较(。另外，您缺少INSERT语句中的VALUES关键字。

编辑：正如Zohar在下面的评论中指出的那样，您应该在VALUES子句之前真正指定目标表的列列表。我们看不到OP的原始问题是什么，所以我使用了自己的最佳猜测来填补它们。

更正错误和格式化代码，我想到：

DECLARE @HighScore AS FLOAT(2);
SET @HighScore = 35;
IF
    (
        SELECT COUNT(Date)FROM data WHERE DATEDIFF(d, Date, '2/2/2018') <= 6 AND Category = 'High'
    ) >
    (
        SELECT COUNT(Date) / 4
        FROM data
        WHERE DATEDIFF(d, Date, '2/2/2018') >= 7
            AND DATEDIFF(d, Date, '2/2/2018') < 35
            AND Category = 'High'
    )
BEGIN
    SET @HighScore = @HighScore - 5;
    INSERT INTO Scoring (Date, Category, Score, PlayerID) 
    VALUES ('2/2/2018', 'High', @HighScore, 1);
END;
ELSE IF
    (
        SELECT COUNT(Date)FROM data WHERE DATEDIFF(d, Date, '2/2/2018') <= 6 AND Category = 'High'
    ) <
    (
        SELECT COUNT(Date) / 4
        FROM data
        WHERE DATEDIFF(d, Date, '2/2/2018') >= 7
            AND DATEDIFF(d, Date, '2/2/2018') < 35
            AND Category = 'High'
    )
BEGIN
    SET @HighScore = @HighScore + 5;
    INSERT INTO Scoring (Date, Category, Score, PlayerID)
    VALUES ('2/2/2018', 'High', @HighScore, 1);
END;
ELSE
BEGIN
    SET @HighScore = @HighScore + 0;
    INSERT INTO Scoring (Date, Category, Score, PlayerID)
    VALUES ('2/2/2018', 'High', @HighScore, 1);
END;