我如何在SQL中使用多列选择强制转换?

Question

我试过这样做来获得我们的收入:

SELECT 
CAST(stays_in_week_nights AS int) + 
CAST(stays_in_weekend_nights AS int) * 
CAST(adr AS int) AS revenue
FROM 
Hotels

我期望得到一个数字作为计算的结果-但相反，我得到了这个错误:

Msg 245, Level 16, State 1, Line 2
将varchar值'111.6'转换为int数据类型失败

Answer 1

类型转换为小数而不是整型。假设adr字段有问题，那么它应该是

SELECT  
(CAST(stays_in_week_nights AS int) + CAST(stays_in_weekend_nights AS int))
* CAST(adr AS decimal(10,2)) AS revenue
FROM Hotels

对于十进制(10,2)的两个参数需要什么取决于您的数据。小数(10,2)在小数点前8位，小数点后2位。

另外，您可能需要添加一组额外的括号，以确保正确地计算它。您是想要(week+weekend) *adr还是week+ (weekend*adr)

您当前将要得到的是第二个。即使这是您想要的，添加括号也会向下一个开发人员澄清这就是您想要做的