时间戳

首页 > 解决方案 > 条件语句下的'expecting EOS'

sql - 条件语句下的'expecting EOS'

问题描述

MS SQL Server 在从 CASE 开始的条件语句下告诉我“期待 EOS”。有人能弄清楚这段代码有什么问题吗？

SELECT 
    goal,
    AVG(pledged) AS avg_num_pledged, 
    AVG(backers) AS avg_num_backers


CASE WHEN currency='GBP' THEN goal*1.3

ELSEIF =currency ='CAD' THEN goal*0.76

ELSEIF currency ='AUD' THEN goal*0.71

ELSEIF currency ='NOK' THEN goal*0.11

ELSEIF currency ='EUR' THEN goal*1.18

ELSEIF currency ='MXN' THEN goal*0.048

ELSEIF currency='SEK' THEN goal*0.11

ELSEIF currency='NZD' THEN goal*0.67

ELSEIF currency='CHF' THEN goal*1.11

ELSEIF currency='DKK' THEN goal*0.16

ELSEIF currency='HKD' THEN goal*0.13

ELSEIF currency='SGD' THEN goal*0.74
ELSEIF currency='JPY' THEN goal*0.0095

ELSE goal

END AS currency_uniformed



FROM kickstarter;

标签： sqlsql-serverconditional-statements

解决方案

如前面的评论所示，CASE语法不正确。您还可以将乘法移到CASE表达式之外。为了获得可运行的查询，还grouping缺少一个子句来处理AVG()跨goal列的聚合 ()。

SELECT goal,
       AVG(
       case currency
          when 'GBP' then 1.3
          when 'CAD' then 0.76
          when 'AUD' then 0.71
          ...
          else 1
        end * goal) AS goal_currency_uniformed,
       AVG(pledged) AS avg_num_pledged, 
       AVG(backers) AS avg_num_backers
FROM kickstarter
GROUP BY goal;

请考虑将汇率移动到单独的表格中。这将使您可以稍微精简此查询，并具有额外的优势，您可以在不修改代码的情况下更新汇率。

SELECT ks.goal,
       AVG(er.rate * ks.goal) as goal_currency_uniformed,
       AVG(pledged) AS avg_num_pledged, 
       AVG(backers) AS avg_num_backers
FROM kickstarter ks
JOIN exchangerates er ON er.currency = ks.currency
GROUP BY goal;

小提琴来看看一切在行动。

推荐阅读