SQL 如何在输出期间更改值名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7692139/
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
How to change a value name during output
提问by Kenh426
I am trying to change the name of a stored value when I execute my SQL script:
我试图在执行 SQL 脚本时更改存储值的名称：
SELECT
PERSONNUM,
PAYCODENAME,
CAST(WFCTIMEINSECONDS AS FLOAT)/3600 AS Total_Hours
FROM
VP_ALLTOTALS
WHERE
Applydate >= '09/25/2011' AND
Applydate <= '10/01/2011' AND
PAYCODENAME IN ('Vacation'
,'Sick Leave - Paid'
,'Personal Business - Paid'
,'Comp Time - Paid'
)
I want the Vacation to be VAC, Sick Leave - Paid to be SIC, Personal Business - Paid to be PER and
我希望假期是 VAC，病假 - 带薪是 SIC，个人业务 - 带薪是 PER 和
回答by JNK
The easiest way is a list of CASEoptions for substitutions.
最简单的方法是列出CASE替换选项。
SELECT PERSONNUM
PAYCODENAME CASE WHEN 'Vacation' THEN 'VAC'
WHEN 'Sick Leave - Paid' THEN 'SIC'
WHEN 'Personal Business - Paid' THEN 'PER'
ELSE PAYCODENAME END AS PAYCODENAME
....
回答by garnertb
Use a CASEstatement:
使用CASE语句：
SELECT
PERSONNUM,
CASE PAYCODENAME
WHEN 'VACATION' THEN 'Vac'
WHEN 'Sick Leave - Paid' THEN 'SIC'
WHEN 'Comp Time - Paid' THEN 'PER'
ELSE PAYCODENAME
END,
cast(WFCTIMEINSECONDS as float)/3600 as Total_Hours
FROM
VP_ALLTOTALS
WHERE
Applydate >= '09/25/2011' AND
Applydate <= '10/01/2011' AND
PAYCODENAME in ('Vacation'
,Sick Leave - Paid'
,'Personal Business - Paid'
,'Comp Time - Paid'
)
回答by Martin Smith
You can use a permanent table, derived table, or common table expression (subject to availability in your RDBMS) with the mappings and join onto that in place of the inlist.
您可以使用具有映射的永久表、派生表或公用表表达式（取决于 RDBMS 中的可用性），并连接到该映射来代替in列表。
WITH PAYCODES(PAYCODENAME, LABEL)
AS (SELECT 'Vacation',
'VAC'
UNION ALL
SELECT 'Sick Leave - Paid',
'SIC'
UNION ALL
SELECT 'Personal Business - Paid',
'PER'
UNION ALL
SELECT 'Comp Time - Paid',
'')
SELECT PERSONNUM,
LABEL AS PAYCODENAME,
CAST(WFCTIMEINSECONDS AS FLOAT) / 3600 AS Total_Hours
FROM VP_ALLTOTALS
JOIN PAYCODES
ON PAYCODES.PAYCODENAME = VP_ALLTOTALS.PAYCODENAME
WHERE Applydate >= '09/25/2011'
AND Applydate <= '10/01/2011'
