sql case语句

whhtz7ly 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(321)

我无法正确地运行案例陈述这里是问题3。从城市表中选择名称。从country表中选择name。创建一个计算字段“gnpoverpop”，用于计算国民生产总值/城市人口，并将该字段的格式设置为6位小数。然后创建另一个计算字段，用于确定以下内容：
如果城市人口为：
大于或等于国家人口的10%，然后将“10%或更多”打印到计算的字段值中
大于或等于国家人口的5%，然后将“5%或更多”打印到计算的字段值中
大于或等于国家人口的1%，然后将“1%或更多”打印到计算的字段值中
不在上述类别中，则将“小于1%”打印到计算字段值中
按以下方式排列结果：
第一个按gnpover pop按降序排列第二个按国家名第三个按城市名排列
您的结果如下所示：

+-----------------------------------+---------------------------------------+------------+-------------------+

| Name                              | name                                  | GNPOverPop | PopPercentageDesc |

+-----------------------------------+---------------------------------------+------------+-------------------+

| Charleston                        | United States                         | 95.558200  | .1% or more       |

| Carson                            | United States                         | 95.530312  | .1% or more       |

| Odessa                            | United States                         | 95.312063  | .1% or more       |

| Elgin                             | United States                         | 95.189469  | .1% or more       |

| Kenosha                           | United States                         | 95.147965  | .1% or more       |

| Fall River                        | United States                         | 93.983767  | .1% or more       |

| Santa Monica                      | United States                         | 93.437925  | .1% or more       |

| Cary                              | United States                         | 93.305779  | .1% or more       |

| Boulder                           | United States                         | 93.280212  | .1% or more       |

| Visalia                           | United States                         | 92.747543  | .1% or more       |

| San Mateo                         | United States                         | 92.710160  | .1% or more       |

| Arden-Arcade                      | United States                         | 92.467405  | .1% or more       |

| Fairfield                         | United States                         | 92.250911  | .1% or more       |

| Gainesville                       | United States                         | 92.215926  | .1% or more       |

| Compton                           | United States                         | 91.646925  | .1% or more       |

| Billings                          | United States                         | 91.524713  | .1% or more       |

| Roanoke                           | United States                         | 91.162955  | .1% or more       |

| Brockton                          | United States                         | 90.874825  | .1% or more       |

这是我的问题

SELECT City.name, Country.Name, FORMAT((Country.GNP/City.Population),6) AS 
'GNPOverPop', 
case Country.Population 
WHEN City.Population>=0.1 THEN '10% OR MORE' 
WHEN City.Population>=0.05 THEN '5% OR MORE' 
WHEN City.Population>=0.01 THEN '1% OR MORE' 
ELSE 'Less than 1%' END AS 'PopPercentageDesc' 
FROM City JOIN Country ON City.Country = Country.Code 
ORDER BY GNPOverPop DESC, Country.Name, City.name

我的case语句结果只返回else“小于1%”。提前谢谢

sql mysql Database case

来源：https://stackoverflow.com/questions/50691764/sql-case-statement-from-two-tables

1条答案

按热度按时间

cbwuti441#

这是我运行的查询，行为是预期的。
查询：

create table city (name varchar(20),popula decimal (5,2), GNP decimal(5,2))
  insert into city 
  values
  ('HYD',0.1,100),
  ('BOM',0.06,30),
  ('CHN',0.02,40),
  ('DEL' ,0.2,50);

  select name,case when popula >= 0.1 then '10% or more'
    when popula >= 0.05 and popula < 0.1 then '5% or more'
    when popula >= 0.01 and popula <0.05 then '1% or more'
    else 'less than 1%' end as PopPercentageDESC
  from city

输出：

name    PopPercentageDESC
HYD    10% or more
BOM    5% or more
CHN    1% or more
DEL    10% or more

赞(0）回复(0）举报 2021-06-20

我来回答

sql case语句

1条答案

相关问题

热门标签

最新问答