SQL根据记录中的其他字段创建字段

发布于 2024-12-26 23:15:55 字数 407 浏览 0 评论 0原文

我想根据另一个字段中的数据在 sql 查询中创建一个字段。例如：

First Name  Last Name  Hometown      State (created column based on Hometown)
Phil        Smith      Brooklyn      NY
Bill        Jones      Manhattan     NY
Abraham     Phillips   Cleveland     OH
Michael     Davis      Cincinnati    OH
William     Brett      Queens        NY

“State”列可以来自查找表或 if/else 语句中。我不知道如何做到这一点，所以我将不胜感激任何帮助。

原文

I would like to create a field in my sql query based on the data in another field. For example:

First Name  Last Name  Hometown      State (created column based on Hometown)
Phil        Smith      Brooklyn      NY
Bill        Jones      Manhattan     NY
Abraham     Phillips   Cleveland     OH
Michael     Davis      Cincinnati    OH
William     Brett      Queens        NY

The "State" column could come from a look-up table or in an if / else statement. I'm not sure how to do this, so I would appreciate any help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

十秒萌定你 2025-01-02 23:15:55

这是一种“解决方案”，还有更多。

您可以创建一个名为“Person”的表，其中包含名字、姓氏和家乡（我想您可能已经拥有该表）以及一个名为“CityToState”的包含城市和州的查找表。

用适当的数据填充查找表（我确信它会很大）并发出查询，

select FirstName, LastName, Hometown, State 
    from Person left join CityToState on Hometown=City;

这应该会为您提供正确的数据，如果查找表中不存在该城市，则为该州返回 NULL。

基本上，它的作用是从 Person 获取所有数据并将其逐行连接到 CityToState 中的行，其中 HomeTown 与 City 相同。 “左”部分意味着即使 CityToState 中没有匹配的行，它也应该返回左表 (Person) 中的行。

This is one "solution", there are many more.

You could create one table called "Person" consisting of FirstName, LastName and Hometown (I presume you may have that table already) and a lookup table called "CityToState" with City and State.

Fill the lookup table with appropriate data (it'll be quite large, I'm sure) and issue the query

select FirstName, LastName, Hometown, State 
    from Person left join CityToState on Hometown=City;

That should give you the correct data, with NULL returned for the state if the city does not exist in the lookup table.

Basically what this does is to get all data from Person and join it, row by row with the row in CityToState where HomeTown is the same as City. The "left" part means that it should return the row from the left table (Person) even if there is no matching row in CityToState.

回复收藏 0 原文