SQL 向表中添加一列，使用 case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31431341/
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 Adding a column to a table, with case statement
提问by Garrick Brim
I have my case statement but would like to add the new column "Unit_Type" to the table...
我有我的案例陈述，但想将新列“Unit_Type”添加到表中...
Unit_type =(case [INSP_UNIT_TYPE_ID]
when '9' then 'Semi Trailer'
when '7' then 'Pole Trailer'
when '6' then 'Other'
when '5' then 'Motor Carrier'
when '3' then 'Full Trailer'
when '2' then 'Dolly Converter'
when '14' then 'Intermodal Chassis'
when '12' then 'Van'
when '11' then 'Truck Tractor'
when '10' then 'Straight Truck'
else 'Invalid'
end) from [dbo].[Insp_Unit_Pub_01012015_05282015];
回答by Gordon Linoff
You seem to be using SQL Server. Just add a computed column:
您似乎正在使用 SQL Server。只需添加一个计算列：
alter table Insp_Unit_Pub_01012015_05282015
add Unit_Type as (case [INSP_UNIT_TYPE_ID]
when '9' then 'Semi Trailer'
when '7' then 'Pole Trailer'
when '6' then 'Other'
when '5' then 'Motor Carrier'
when '3' then 'Full Trailer'
when '2' then 'Dolly Converter'
when '14' then 'Intermodal Chassis'
when '12' then 'Van'
when '11' then 'Truck Tractor'
when '10' then 'Straight Truck'
else 'Invalid'
end);
This does the calculation when you fetch the value. The advantage is that you don't have to write a trigger to change the value when INSP_UNIT_TYPE_IDchanges.
这会在您获取值时进行计算。好处是你不必写触发器来改变值INSP_UNIT_TYPE_ID。
By the way, this question suggests that you need a reference table for INSP_UNIT_TYPE, with INSP_UNIT_TYPE_IDas the primary key. A JOINis actually a better way to get the value (although using a computed column with a JOINis rather cumbersome).
顺便说一下，这个问题表明您需要一个参考表INSP_UNIT_TYPE，以INSP_UNIT_TYPE_ID作为主键。AJOIN实际上是获取值的更好方法（尽管使用带有 a 的计算列JOIN相当麻烦）。
回答by Paolo
don't do that and use a view.
不要这样做并使用视图。
the above suggestion is the result of a wild guess because you provide neither context nor database structure so it may be plain wrong or not suitable for your situation.
上述建议是胡乱猜测的结果，因为您既没有提供上下文也没有提供数据库结构，因此它可能完全错误或不适合您的情况。
your untold requirement looks like some kind of visualization of the table with human readable data and that's what views are good for.
您不为人知的需求看起来像是具有人类可读数据的表格的某种可视化，而这正是视图的优势所在。
create a view adding the required joins or including the CASE statement you are trying to put in the table (check the syntax ^^):
创建一个视图，添加所需的连接或包含您尝试放入表中的 CASE 语句（检查语法 ^^）：
CREATE VIEW [name of the view here]
AS
SELECT field1,
field2,
field3,
Unit_type = CASE [INSP_UNIT_TYPE_ID]
WHEN '9' THEN 'Semi Trailer'
WHEN '7' THEN 'Pole Trailer'
WHEN '6' THEN 'Other'
WHEN '5' THEN 'Motor Carrier'
WHEN '3' THEN 'Full Trailer'
WHEN '2' THEN 'Dolly Converter'
WHEN '14' THEN 'Intermodal Chassis'
WHEN '12' THEN 'Van'
WHEN '11' THEN 'Truck Tractor'
WHEN '10' THEN 'Straight Truck'
ELSE 'Invalid'
END
FROM [dbo].[Insp_Unit_Pub_01012015_05282015];
as suggested by some comment you may put a JOINin the query instead of the CASEstatement if the human readable text comes from a lookup table.
正如某些评论所建议的JOIN那样，CASE如果人类可读的文本来自查找表，您可以在查询中放置 a而不是语句。
