如何修改SQL临时列DeathPercentage的数据类型为decimal？

当然可以。问题根源在于：total_deaths和population都是整数类型（比如bigint），SQL中整数之间的除法会默认返回整数结果（直接丢弃小数部分），再乘以100后依然是整数，所以最终DeathPercentage被自动设为bigint类型。

要解决这个问题，只需要在计算前将其中一个（或两个）整数列转换为decimal类型，这样运算过程会保留小数精度，最终结果也会是decimal类型。以下是两种修改后的查询语句：

方法一：转换分子为decimal #

SELECT 
  location, 
  total_deaths, 
  population.population, 
  (CAST(total_deaths AS DECIMAL(18,2)) / population.population) * 100 AS DeathPercentage 
FROM covid_deaths
LEFT OUTER JOIN population
ON covid_deaths.location = population.country
WHERE covid_deaths.continent != 'null'

方法二：转换分母为decimal #

SELECT 
  location, 
  total_deaths, 
  population.population, 
  (total_deaths / CAST(population.population AS DECIMAL(18,2))) * 100 AS DeathPercentage 
FROM covid_deaths
LEFT OUTER JOIN population
ON covid_deaths.location = population.country
WHERE covid_deaths.continent != 'null'

其中DECIMAL(18,2)表示总长度18位、保留2位小数，你可以根据实际数据规模调整精度（比如用DECIMAL(20,4)来保留更多小数位）。

内容的提问来源于stack exchange，提问作者Zecharia Paras