SQL选择用字符串替换整数 [英] SQL select replace integer with string

问题描述

目标是用数字表示的char值替换SQL查询中返回的整数值。例如：

Goal is to replace a integer value that is returned in a SQL query with the char value that the number represents. For example:

标记为运动的表属性定义为1-4之间的整数。 1 =篮球，2 =曲棍球，等等。下面是数据库表，然后是所需的输出。

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.

数据库表：

Player     Team     Sport
--------------------------
Bob        Blue     1
Roy        Red      3
Sarah      Pink     4 

所需输出：

Player     Team     Sport
------------------------------
Bob        Blue     Basketball
Roy        Red      Soccer
Sarah      Pink     Kickball

将这些整数值转换为字符串值的最佳实践是什么？在传递给程序之前使用SQL转换值？使用脚本语言来更改程序中的值？更改数据库设计吗？

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?

推荐答案

数据库应该保存这些值，并且应该执行联接另一个包含该数据的表的操作。

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名称FavSport

1 Alex 4

2 Gnats 2

ID Name FavSport
1 Alex 4
2 Gnats 2

包含运动项目列表的表

ID Sport

1篮球

2足球

3足球

4踢球

ID Sport
1 Basketball
2 Football
3 Soccer
4 Kickball

然后您将在这些表之间进行联接

Then you would do a join between these tables

select people.name, sports.sport 
from people, sports 
where people.favsport = sports.ID

这会给你回来

Name Sport

Alex Kickball

Gnat Football

Name Sport
Alex Kickball
Gnat Football

您可以所以使用案例声明。只是从上面使用人员表，您就可以这样写

You could also use a case statement eg. just using the people table from above you could write something like

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.

这篇关于SQL选择用字符串替换整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！