mysql - 我的 Select Case 有问题，输出不工作

Question

SELECT date,
       (CASE country WHEN 'France' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS france,
       (CASE country WHEN 'Germany' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS germany,
       (CASE country WHEN 'Italy' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS italy,
       (CASE country WHEN 'Netherlands' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS netherlands,
       (CASE country WHEN 'Nordic' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS nordic,
       (CASE country WHEN 'Spain' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS spain,
       (CASE country WHEN 'UK' THEN (sum(duration) / sum(scheduled))*100 ELSE 0 END) AS uk
FROM report_adherence_daily
WHERE date>= '2016-07-04'
  AND date<= '2016-07-10'
GROUP BY date,
         country

上面是我将数据转换为我需要的格式的代码，但结果显示为

.

我缺少的是使结果看起来像的额外位

.

我曾尝试在案例之前和案例中使用 max，但没有奏效。如果我只按日期分组，我会丢失大部分数据，这与按国家/地区分组相同。我错过了什么？

Answer 1

你想要条件聚合，但 case 需要进入 sum() 内部，而不是外部:

SELECT date,
       (100 * sum(case when country = 'France' then duration end) /
        sum(case when country = 'France' then scheduled end)
       ) as france,
       . . .
FROM report_adherence_daily
WHERE date >= '2016-07-04' AND date <= '2016-07-10'
GROUP BY date
ORDER BY date;