mysql - 我想在 Where 条件中输入 2 个日期，一次用于 8 月，一次用于 10 月

Question

我想在 Where 条件中输入 2 个日期，一次用于 8 月，一次用于 10 月。不幸的是，我在这里没有进一步说明，因为我只能在 Where 条件中输入日期（从 .. 到），请参阅 AND 日期之间。

SELECT
  subregion1_code,
  subregion1_name,
  SUM(new_confirmed) as august_confirmed,
  SUM(new_tested) as august_tested,
  SUM(new_confirmed) as october_confirmed,
  SUM(new_tested) as october_tested,
  SUM(new_confirmed) / SUM(new_tested) AS positive_test_rate, 
FROM
  `bigquery-public-data.covid19_open_data.covid19_open_data` as B
WHERE
  country_code = "US"
  AND aggregation_level = 1
  AND date BETWEEN '2020-10-01' AND '2020-10-30'
 
GROUP BY
  subregion1_code,
  subregion1_name 
ORDER BY
  positive_test_rate DESC

标签： mysqlgoogle-bigquery

Answer 1

您可以使用条件聚合来做到这一点：

SELECT
  subregion1_code,
  subregion1_name,
  SUM(CASE WHEN MONTH(date) = 8 THEN new_confirmed ELSE 0 END) AS august_confirmed,
  SUM(CASE WHEN MONTH(date) = 8 THEN new_tested ELSE 0 END) AS august_tested,
  SUM(CASE WHEN MONTH(date) = 10 THEN new_confirmed ELSE 0 END) AS october_confirmed,
  SUM(CASE WHEN MONTH(date) = 10 THEN new_tested ELSE 0 END) AS october_tested,
  SUM(new_confirmed) / SUM(new_tested) AS positive_test_rate, 
FROM `bigquery-public-data.covid19_open_data.covid19_open_data` AS B
WHERE
  country_code = 'US'
  AND aggregation_level = 1
  AND (date BETWEEN '2020-10-01' AND '2020-10-31' OR date BETWEEN '2020-08-01' AND '2020-08-31')
GROUP BY subregion1_code, subregion1_name 
ORDER BY positive_test_rate DESC