条件语句下的“期望EOS”

Question

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;

Answer 1

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

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;

Fiddle查看所有实际操作的内容。