SQL Server中的四舍五入(向上向下取整)和CAST以及CONVERT

事情的起因

力扣传送门

力扣传送门

我的解

SELECT  
request_at AS Day,
CONVERT(DECIMAL(18,2),ROUND((CONVERT(DECIMAL(18,2),COUNT(CASE  WHEN STATUS!='completed ' THEN 1 end))
/CONVERT(DECIMAL(18,2),COUNT(CASE  WHEN  b.banned!='Yes' AND c.banned!='Yes' THEN 1 end))),2))  AS [Cancellation Rate]
FROM Trips a LEFT JOIN users b ON a.driver_id=b.users_id LEFT JOIN users c c.client_id =b.users_id
WHERE request_at>='2013-10-01' AND request_at<='2013-10-03'
GROUP BY request_at

最优解

select request_at Day, round(avg(case when status = 'cancelled_by_driver' or status ='cancelled_by_client'
THEN 1.00 else 0.00 end),2) 'Cancellation Rate'
from trips t join users u on t.client_id = u.users_id join users u2 on t.driver_id = u2.users_id
WHERE u.banned ='No' and u2.banned = 'No' 
AND request_at between '2013-10-01' and '2013-10-03' group by request_at

分析

这个题目很明显，在处理四舍五入方面，我处理的复杂了很多，也确实没有想到avg可以免去多写一个分母，BETWEEN…AND是想到了，但是没有写，这里更推荐使用BETWEEN…AND不推荐>=,然后还有一个要注意的就是这题它的别名中间是有一个空格的，[Cancellation Rate]或者’Cancellation Rate’都行

然后我们可以复习一下四舍五入(向上向下取整) 和CAST()以及CONVERT()

CAST()、CONVERT()

--CASE()、CONVERT()用来转换数据类型的
--int转decimal
SELECT CAST(1 AS DECIMAL(18,2))
SELECT CONVERT (DECIMAL(18,2),1)

ROUND()、CEILING()、FLOOR()

--四舍五入 2为小数位
SELECT ROUND(18.225,2)
--向上取整
SELECT CEILING(18.225)
--向下取整
SELECT FLOOR(18.225)