创建新列的sql用例

yx2lnoni 于 2021-08-01 发布在 Java

关注(0)|答案(2)|浏览(302)

我尝试使用case语句，根据“降水量类型”中的值，将“数量”列拆分为两个新列（总降雨量和总降雪量之和），以查找一个城市一年的总降雨量和总降雪量。

select city
  ,(CASE precipitation_type WHEN 'rain' THEN total ELSE 0 END) AS "rainfall"
  ,(CASE precipitation_type WHEN 'snow' THEN total ELSE 0 END) AS "snowfall"
from 
  (select city, precipitation_type, sum(amount) as total
  from precipitation
  group by city, precipitation_type) a
group by city, a.precipitation_type, a.total
order by city asc;

我的当前输出如下所示：

Albany      0.00    48.51
Baltimore   0.00    0.00
Baltimore   0.00    27.11
Boston      0.00    0.00
Boston      41.12   0.00
Boston      0.00    32.28

我希望每个城市的总降雨量和总降雪量保持一致。前任：

Albany       38.54   48.51
Baltimore    40.97   27.11
Boston       41.12   32.28

sql case

来源：https://stackoverflow.com/questions/62669322/sql-use-case-to-create-new-columns

2条答案

按热度按时间

fdbelqdn1#

一个简单的条件聚合应该返回您期望的输出。虽然您没有提到您的dbms，但下面的脚本应该适合您-

SELECT city, 
SUM(CASE precipitation_type WHEN 'rain' THEN amount ELSE 0 END) AS "rainfall",
SUM(CASE precipitation_type WHEN 'snow' THEN amount ELSE 0 END) AS "snowfall"
FROM precipitation
GROUP BY city

赞(0）回复(0）举报 2021-08-01

6pp0gazn2#

你在找这样的东西吗？

select city
  , SUM(CASE precipitation_type WHEN 'rain' THEN total ELSE 0 END) AS rainfall
  , SUM(CASE precipitation_type WHEN 'snow' THEN total ELSE 0 END) AS snowfall
from 
  (select city, precipitation_type, sum(amount) as total
  from precipitation
  group by city, precipitation_type) a
group by city
order by city asc

赞(0）回复(0）举报 2021-08-01

我来回答

创建新列的sql用例

2条答案

相关问题

热门标签

最新问答