T-SQL——关于求百分比

目录

shanzm-2024-4-16 21:21:45

1.SQL取百分比

示例0：求1/3后取百分比，保留两位小数

SELECT CAST(CAST(1 * 1.0 * 100 / 3 AS DECIMAL(10, 2)) AS VARCHAR(50)) + '%';
--SqlServer2012版本之后：
SELECT FORMAT(1*1.0/3, 'P') Result;
---
33.33%
33.33%

示例1：最基本的两列百分比


WITH temp AS 
(
SELECT 1 AS NumA, 3 AS NumB 
)
SELECT CAST( CAST( temp.NumA*1.0*100/temp.NumB AS DECIMAL(10,2)) AS VARCHAR(50))+'%' FROM temp;
---
33.33%

示例2：关于处理除零错误


--首先：0/NULL=NULL
SELECT 0/NULL;--NULL

--其次：NULLIF(a,b) 表示：若a等于b,则返回NULL，否则返回a
SELECT  0/NULLIF(0,0)--NULL

--最后：ISNULL判断是否是NULL，若是返回0
SELECT ISNULL(0/NULLIF(0,0),0)--0


WITH temp AS 
(
SELECT 1 AS NumA, 3 AS NumB 
UNION ALL
SELECT 1 AS NumA, 0 AS NumB 
)
SELECT CAST( ISNULL( CAST( temp.NumA*1.0*100/ NULLIF( temp.NumB,0) AS DECIMAL(10,2)),0) AS VARCHAR(50))+'%'  AS 百分比 FROM temp;

---结果：

百分比
-----------
33.33%
0.00%

2.C#取百分比


decimal percent = 0.3333333M;

string q1 = percent.ToString("P"); //33.33%  自动四舍五入，百分数，千位分隔，自动两个小数
string q2 = percent.ToString("P0"); //33%    P后边跟数字，代表精度。
string q3 = percent.ToString("P1"); //33.3%
string q4 = percent.ToString("P2"); //33.33%
string q5 = percent.ToString("P3"); //33.333%
string q6 = percent.ToString("P4"); //33.3333%
string q7 = percent.ToString("P5"); //33.33333%
string q8 = percent.ToString("P6"); //33.333330%

3.JS取百分比

//两数相除取百分比%并保留两位小数
function Percentage(number1, number2) 
{ 
    return (Math.round(number/number2 * 10000) / 100.00 + "%");    // 小数点后两位百分比
}
var res = Percentage(1, 3)
console.log(res)//打印：33.33%
posted @ 2024-04-16 17:23  shanzm  阅读(6)  评论(0)  编辑  收藏  举报
TOP