在SQL中截取/四舍五入varchar列

huangapple
go评论107阅读模式
英文:

Truncate/Round varchar columns in SQL

问题

我有2列Total和OccupiedBeds，它们的数据类型是varchar。

我需要计算Occupancy = OccupiedBeds/Total，使用下面的代码：

  1. CASE 
  2.     WHEN isnull(cast([OccupiedBeds] AS INT), 0) = 0
  3.         THEN 0
  4.     ELSE cast([Total] AS float) / (cast([OccupiedBeds] AS float) / 1000)
  5.     END AS Occupancy

输出：

  1. Total OccupiedBeds Occupancy
  2. 0     0            0.00000000000000
  3. 0     0            0.00000000000000
  4. 0     0            0.00000000000000
  5. 8     736          10.86956521739130
  6. 7     638          10.97178683385579
  7. 4     367          10.89918256130790
  8. 6     460          13.04347826086956

我需要截断/四舍五入Occupancy，以便在图表上显示为百分比。

我尝试将float替换为decimal(19,2)，但结果如下所示：

  1. Total OccupiedBeds Occupancy
  2. 0     0            0
  3. 0     0            0
  4. 8     736          10.87
  5. 7     638          10.97
  6. 4     367          10.90
  7. 6     460          13.04

有什么方法可以实现这个目标吗？

英文:

I have 2 columns Total and OccupiedBeds which are varchar datatype.

I need to calculate Occupancy = OccupiedBeds/Total and using below code:

  1. CASE 
  2. 		WHEN isnull(cast([OccupiedBeds] AS INT), 0) = 0
  3. 			THEN 0
  4. 		ELSE cast([Total] AS float) / (cast([OccupiedBeds] AS float) / 1000)
  5. 		END AS Occupancy

o/p:

  1. Total OccupiedBeds Occupancy
  2. 0	       0	0.00000000000000
  3. 0	       0	0.00000000000000
  4. 0	       0	0.00000000000000
  5. 8	      736	10.86956521739130
  6. 7	      638	10.97178683385579
  7. 4	      367	10.89918256130790
  8. 6	      460	13.04347826086956

I need to truncate/Round the Occupancy so it can be shown as % on a chart.

I tried replacing float with decimal(19,2) but showing below results:

  1. Total OccupiedBeds Occupancy
  2. 0	         0	    0
  3. 0	         0	    0
  4. 8	         736	10.8695652173913
  5. 7	         638	10.9717868338558
  6. 4	         367	10.8991825613079
  7. 6	         460	13.0434782608696

Any ideas how to accomplish this?

答案1

得分: 2

你基本上可以使用 ROUND 函数，而不需要更改数据类型：

  1. CASE 
  2.     WHEN isnull(cast([OccupiedBeds] AS INT), 0) = 0
  3.         THEN 0
  4.     ELSE ROUND((cast([Total] AS float) / (cast([OccupiedBeds] AS float) / 1000)), 2)
  5.     END AS Occupancy

UPD：

你的 Occupancy 公式看起来也有点奇怪，也许你需要计算为 OccupiedBeds / Total * 100：

  1. CASE 
  2.     WHEN isnull(cast([Total] AS INT), 0) = 0
  3.         THEN 0
  4.     ELSE ROUND((cast([OccupiedBeds] AS float) / cast([Total] AS float) * 100), 2)
  5.     END AS Occupancy
英文:

You can basically use the ROUND function, rather than changing the data type:

  1. CASE 
  2.     WHEN isnull(cast([OccupiedBeds] AS INT), 0) = 0
  3.         THEN 0
  4.     ELSE ROUND((cast([Total] AS float) / (cast([OccupiedBeds] AS float) / 1000)), 2)
  5.     END AS Occupancy

UPD:

Your Occupancy formula looks a bit strange too, perhaps you need to calculate it as OccupiedBeds / Total * 100:

  1. CASE 
  2.     WHEN isnull(cast([Total] AS INT), 0) = 0
  3.         THEN 0
  4.     ELSE ROUND((cast([OccupiedBeds] AS float) / cast([Total] AS float) * 100), 2)
  5.     END AS Occupancy

huangapple
  • 本文由 发表于 2023年7月13日 21:59:27
  • 转载请务必保留本文链接：https://go.coder-hub.com/76680224.html
  • decimal
  • rounding
  • sql
  • tsql
  • varchar
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定