SQL SQL选择用字符串替换整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/924442/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL select replace integer with string
提问by Cimplicity
Goal is to replace a integer value that is returned in a SQL query with the char value that the number represents. For example:
目标是将 SQL 查询中返回的整数值替换为该数字所代表的字符值。例如：
A table attribute labeled ‘Sport' is defined as a integer value between 1-4. 1 = Basketball, 2 = Hockey, etc. Below is the database table and then the desired output.
标记为“运动”的表属性定义为 1-4 之间的整数值。1 = 篮球，2 = 曲棍球，等等。下面是数据库表，然后是所需的输出。
Database Table:
数据库表：
Player Team Sport
--------------------------
Bob Blue 1
Roy Red 3
Sarah Pink 4
Desired Outputs:
期望的输出：
Player Team Sport
------------------------------
Bob Blue Basketball
Roy Red Soccer
Sarah Pink Kickball
What is best practice to translate these integer values for String values? Use SQL to translate the values prior to passing to program? Use scripting language to change the value within the program? Change database design?
将这些整数值转换为字符串值的最佳实践是什么？在传递给程序之前使用 SQL 来转换值吗？使用脚本语言更改程序内的值？更改数据库设计？
回答by Alex Andronov
The database should hold the values and you should perform a join to another table which has that data in it.
数据库应该保存这些值，并且您应该连接到另一个包含该数据的表。
So you should have a table which has say a list of people
所以你应该有一张表，上面写着一个人的名单
ID Name FavSport
1 Alex 4
2 Gnats 2
ID 名称 FavSport
1 Alex 4
2 Gnats 2
And then another table which has a list of the sports
然后是另一个包含运动列表的表格
ID Sport
1 Basketball
2 Football
3 Soccer
4 Kickball
ID 运动
1 篮球
2 足球
3 足球
4 踢球
Then you would do a join between these tables
然后你会在这些表之间进行连接
select people.name, sports.sport
from people, sports
where people.favsport = sports.ID
which would give you back
这会让你回来
Name Sport
Alex Kickball
Gnat Football
名称运动
Alex Kickball
Gnat Football
You could also use a case statement eg. just using the people table from above you could write something like
您也可以使用 case 语句，例如。只需使用上面的人表，你就可以写出类似的东西
select name,
case
when favsport = 1 then 'Basketball'
when favsport = 2 then 'Football'
when favsport = 3 then 'Soccer'
else 'Kickball'
end as "Sport"
from people
But that is certainly not best practice.
但这肯定不是最佳实践。
回答by Cimplicity
MySQL has a CASEstatement. The following works in SQL Server:
MySQL 有一个CASE声明。以下在 SQL Server 中有效：
SELECT
CASE MyColumnName
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END
回答by Cimplicity
In oracle you can use the DECODEfunction which would provide a solution where the design of the database is beyond your control.
在 oracle 中，您可以使用DECODE功能，该功能将提供一种解决方案，其中数据库的设计超出了您的控制范围。
Directly from the oracle documentation:
直接来自oracle文档：
Example: This example decodes the value warehouse_id. If warehouse_id is 1, then the function returns 'Southlake'; if warehouse_id is 2, then it returns 'San Francisco'; and so forth. If warehouse_id is not 1, 2, 3, or 4, then the function returns 'Non domestic'.
示例：本示例对值warehouse_id 进行解码。如果仓库 ID 为 1，则该函数返回 'Southlake'；如果仓库 ID 为 2，则返回“旧金山”；等等。如果warehouse_id 不是1、2、3 或4，则该函数返回“非家庭”。
SELECT product_id,
DECODE (warehouse_id, 1, 'Southlake',
2, 'San Francisco',
3, 'New Jersey',
4, 'Seattle',
'Non domestic') "Location"
FROM inventories
WHERE product_id < 1775
ORDER BY product_id, "Location";
回答by Alex Martelli
The CASEexpression could help. However, it may be even faster to have a small table with an int primary key and a namestring such as
该CASE表达可以帮助。但是，拥有一个带有 int 主键和一个name字符串的小表可能会更快，例如
1 baseball
2 football
etc, and JOINit appropriately in the query.
等，并JOIN在查询中适当地使用它。
回答by Ryan
Do you think it would be helpful to store these relationships between integers and strings in the database itself? As long as you have to store these relationships, it makes sense to store it close to your data (in the database) instead of in your code where it can get lost. If you use this solution, this would make the integer a foreign key to values in another table. You store integers in another table, say sports, with sport_idand sport, and join them as part of your query.
您认为将整数和字符串之间的这些关系存储在数据库本身中会有所帮助吗？只要您必须存储这些关系，就可以将其存储在靠近数据（在数据库中）的地方，而不是存储在可能会丢失的代码中。如果您使用此解决方案，这将使整数成为另一个表中值的外键。您将整数存储在另一个表中，比如sports，sport_id和sport，并将它们作为查询的一部分加入。
Instead of SELECT * FROM my_tableyou would SELECT * from my_tableand use the appropriate join. If not every row in your main column has a corresponding sport, you could use a left join, otherwise selecting from both tables and using = in the where clause is probably sufficient.
而不是SELECT * FROM my_table你会SELECT * from my_table并使用适当的 join。如果不是主列中的每一行都有相应的运动，则可以使用左连接，否则从两个表中进行选择并在 where 子句中使用 = 可能就足够了。
回答by SCady
definitely have the DB hold the string values. I am not a DB expert by any means, but I would recommend that you create a table that holds the strings and their corresponding integer values. From there, you can define a relationship between the two tables and then do a JOIN in the select to pull the string version of the integer.
肯定让数据库保存字符串值。我无论如何都不是数据库专家，但我建议您创建一个包含字符串及其相应整数值的表。从那里，您可以定义两个表之间的关系，然后在选择中执行 JOIN 以提取整数的字符串版本。
tblSport Columns
------------
SportID int (PK, eg. 12)
SportName varchar (eg. "Tennis")
tblFriend Columns
------------
FriendID int (PK)
FriendName (eg. "Joe")
LikesSportID (eg. 12)
In this example, you can get the following result from the query below:
SELECT FriendName, SportName
FROM tblFriend
INNER JOIN tblSport
ON tblFriend.LikesSportID = tblSport.SportID
Man, it's late - I hope I got that right. by the way, you should read up on the different types of Joins - this is the simplest example of one.
伙计，已经晚了 - 我希望我做对了。顺便说一下，您应该阅读不同类型的连接 - 这是一个最简单的示例。
