CASE语句SQL Server 2012 [英] CASE Statement SQL Server 2012
问题描述
使用pubs db,我已使用UNION ALL创建了以下内容,但尝试使用CASE stmt进行以下操作.
Using pubs db I have created the following with UNION ALL, but was trying to do the same with a CASE stmt.
SELECT
t.title_id AS 'Title ID',
t.ytd_sales 'YTD Sales',
t.price AS 'Original Price',
'New Price' = CASE t.ytd_sales
WHEN (t.ytd_sales < 2500.00) THEN CONVERT(DECIMAL(9,2),ROUND (t.price*1.15,2))
WHEN (t.ytd_sales BETWEEN 2500.00 AND 10000.00) THEN CONVERT(DECIMAL(9,2),ROUND(t.price*1.10,2))
WHEN (t.ytd_sales > 10000.00) THEN CONVERT(DECIMAL(9,2),ROUND(t.price*1.05,2))
ELSE CONVERT(DECIMAL(9,2),ROUND(t.price*1.00,2))
END
FROM titles t
;
它不喜欢比较/特殊运算符.这甚至可以作为CASE stmt来做吗? 谢谢
It does not like to comparison/special operators. is this even possible to do as a CASE stmt? Thanks
推荐答案
语法上,正如马丁史密斯(Martin Smith)所指出的,此问题与
Syntactically, as Martin Smith indicates, this question is a duplicate of SQL Server CASE .. WHEN .. expression.
此查询的正确语法应为
SELECT
t.title_id AS [Title ID],
t.ytd_sales AS [YTD Sales],
t.price AS [Original Price],
CASE
WHEN (t.ytd_sales < 2500.00) THEN CONVERT(DECIMAL(9,2),ROUND (t.price*1.15,2))
WHEN (t.ytd_sales BETWEEN 2500.00 AND 10000.00) THEN CONVERT(DECIMAL(9,2),ROUND(t.price*1.10,2))
WHEN (t.ytd_sales > 10000.00) THEN CONVERT(DECIMAL(9,2),ROUND(t.price*1.05,2))
ELSE CONVERT(DECIMAL(9,2),ROUND(t.price*1.00,2))
END AS [New Price]
FROM dbo.titles t
;
冒着被鼓吹的风险:列别名应放在方括号(as [Title ID]
)中,而不是单引号(用于字符串文字).参考: TSQL SELECT子句
At the risk of being preachy: column aliases should be in square brackets (as [Title ID]
), not single quotes (that are for string literals). Ref: TSQL SELECT Clause
这篇关于CASE语句SQL Server 2012的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!