CASE语句SQL Server 2012 [英] CASE Statement SQL Server 2012

查看:220
本文介绍了CASE语句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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆