首先来一个JAVASCRIPT 版本
JAVASCRIPT版修约函数
1 <!--
2 //############################################################
3 // 修约函数 dData 要修约的数， dPrecision 修约间隔，
4 //############################################################
5 function DataTrim(dData,dPrecision)
6 {
7 var dRemainder; //求模的余数部分
8 var lTimes; //求模的整数部分
9 var tmprst,tmprst2;
10 var pos; //移位后小数点的位置
11 var posLStr; //移位后小数点左边字符
12 var posRStr;//移位后小数点右边第一位字符
13 var posRStr2; //移位后小数点右边第二位以后字符
14 var iCnt=0;
15 var tmpFlag = true; ////正负数的判断的标志 true为正数
16 tmpFlag = (dData >= 0);
17 dData = Math.abs(dData);
18 if (dData == null || dData.length==0) return '';
19 if (!IsPrecisionValid(dPrecision)) return "修约间隔错误"; //检查修约间隔的合法性
20 if (dPrecision==0.2 || dPrecision==0.5 ) //如果是0.2和0.5修约
21 {
22 tmprst2 = DataTrim(dData / dPrecision ,1);
23 return (tmprst2*dPrecision).toFixed(1);
24 }
25 else
26 {
27 dData = dData.toFixed(9);
28 tmprst= (dData / dPrecision).toFixed(9).toString();
29 pos = tmprst.indexOf(".");
30 posLStr = parseInt(tmprst.substr(pos-1,1));
31 posRStr = parseInt(tmprst.substr(pos+1,1));
32 posRStr2 = parseInt(tmprst.substr(pos+2));
33 if (posRStr== 5 && (posRStr2 == 0 || isNaN(posRStr2))) // 执行奇数进一，偶数舍弃
34 {
35 if (posLStr % 2 != 0) posLStr += 1;
36 }
37 else //执行4舍5入
38 {
39 if (posRStr >=5) posLStr+= 1;
40 }
41 if (dData >=1)
42 {
43 tmprst = parseFloat((parseInt(tmprst.substr(0,pos-1))*10 + posLStr).toString()+".0");
44 }
45 else
46 {
47 tmprst = parseFloat(tmprst.substr(0,pos-1) + posLStr.toString()+".0");
48 }
49 tmprst *= dPrecision;
50 tmprst = tmpFlag ? tmprst: tmprst*(-1); //正负数的判断
51 for(iCnt=0;dPrecision*Math.pow(10,iCnt) < 1;iCnt++){} //获取修约精度的小数点后位数
52 tmprst = tmprst.toFixed(iCnt);
53 return tmprst;
54 }
55 }
56 //############################################################
57 // 检查修约间隔的合法性 返回值 true or false
58 // dPrecision 修约间隔指示值 改值不能为空，为空 返回false
59 // 一般为 整数或小数 10,100,0.1,0.2,0.5 改值不能为0
60 //############################################################
61 function IsPrecisionValid(dPrecision)
62 {
63 var tmprst;
64 if (dPrecision == null || dPrecision.length == 0 || dPrecision==0)
65 {
66 return false;
67 }
68 if ( dPrecision>=10 && (dPrecision % 10 == 0 || dPrecision % 2==0 || dPrecision % 5== 0))
69 {
70 return true;
71 }
72 else if (dPrecision == 1)
73 {
74 return true;
75 }
76 else if(dPrecision<1)
77 {
78 if (dPrecision==0.2 || dPrecision == 0.5)
79 {
80 return true;
81 }
82 else
83 {
84 while (dPrecision<1)
85 {
86 dPrecision = dPrecision * 10;
87 }
88 return (dPrecision == 1);
89 }
90 }
91 return false;
92 }
2 //############################################################
3 // 修约函数 dData 要修约的数， dPrecision 修约间隔，
4 //############################################################
5 function DataTrim(dData,dPrecision)
6 {
7 var dRemainder; //求模的余数部分
8 var lTimes; //求模的整数部分
9 var tmprst,tmprst2;
10 var pos; //移位后小数点的位置
11 var posLStr; //移位后小数点左边字符
12 var posRStr;//移位后小数点右边第一位字符
13 var posRStr2; //移位后小数点右边第二位以后字符
14 var iCnt=0;
15 var tmpFlag = true; ////正负数的判断的标志 true为正数
16 tmpFlag = (dData >= 0);
17 dData = Math.abs(dData);
18 if (dData == null || dData.length==0) return '';
19 if (!IsPrecisionValid(dPrecision)) return "修约间隔错误"; //检查修约间隔的合法性
20 if (dPrecision==0.2 || dPrecision==0.5 ) //如果是0.2和0.5修约
21 {
22 tmprst2 = DataTrim(dData / dPrecision ,1);
23 return (tmprst2*dPrecision).toFixed(1);
24 }
25 else
26 {
27 dData = dData.toFixed(9);
28 tmprst= (dData / dPrecision).toFixed(9).toString();
29 pos = tmprst.indexOf(".");
30 posLStr = parseInt(tmprst.substr(pos-1,1));
31 posRStr = parseInt(tmprst.substr(pos+1,1));
32 posRStr2 = parseInt(tmprst.substr(pos+2));
33 if (posRStr== 5 && (posRStr2 == 0 || isNaN(posRStr2))) // 执行奇数进一，偶数舍弃
34 {
35 if (posLStr % 2 != 0) posLStr += 1;
36 }
37 else //执行4舍5入
38 {
39 if (posRStr >=5) posLStr+= 1;
40 }
41 if (dData >=1)
42 {
43 tmprst = parseFloat((parseInt(tmprst.substr(0,pos-1))*10 + posLStr).toString()+".0");
44 }
45 else
46 {
47 tmprst = parseFloat(tmprst.substr(0,pos-1) + posLStr.toString()+".0");
48 }
49 tmprst *= dPrecision;
50 tmprst = tmpFlag ? tmprst: tmprst*(-1); //正负数的判断
51 for(iCnt=0;dPrecision*Math.pow(10,iCnt) < 1;iCnt++){} //获取修约精度的小数点后位数
52 tmprst = tmprst.toFixed(iCnt);
53 return tmprst;
54 }
55 }
56 //############################################################
57 // 检查修约间隔的合法性 返回值 true or false
58 // dPrecision 修约间隔指示值 改值不能为空，为空 返回false
59 // 一般为 整数或小数 10,100,0.1,0.2,0.5 改值不能为0
60 //############################################################
61 function IsPrecisionValid(dPrecision)
62 {
63 var tmprst;
64 if (dPrecision == null || dPrecision.length == 0 || dPrecision==0)
65 {
66 return false;
67 }
68 if ( dPrecision>=10 && (dPrecision % 10 == 0 || dPrecision % 2==0 || dPrecision % 5== 0))
69 {
70 return true;
71 }
72 else if (dPrecision == 1)
73 {
74 return true;
75 }
76 else if(dPrecision<1)
77 {
78 if (dPrecision==0.2 || dPrecision == 0.5)
79 {
80 return true;
81 }
82 else
83 {
84 while (dPrecision<1)
85 {
86 dPrecision = dPrecision * 10;
87 }
88 return (dPrecision == 1);
89 }
90 }
91 return false;
92 }
这里是测试JAVASCRIPT 版本的测试结果：
JAVASCRIPT修约函数测试结果
1 -1.99999 to 0.01 = -2.00
2 0.350 to 0.1 = 0.4
3 1.050 to 0.1 = 1.0
4 12.999345 to 0.01 = 13.00
5 5 to 0.001 = 5.000
6 0.0325 to 0.001 = 0.032
7 -930 to 20 = -920
8 842 to 20 = 840
9 830 to 20 = 840
10 60.25 to 0.5 = 60.0
11 60.38 to 0.5 = 60.5
12 -60.75 to 0.5 = 61.0
13 0.777777777 to 0.1 = 0.8
14 0.66666666 to 0.1 = 0.7
15 0.333333333 to 0.1 = 0.3
16 0.333333333 to 0.01 = 0.33
17 0.623333 to 0.01 = 0.62
18 123.465 to 0.1 = 123.5
19 123.457 to 0.01 = 123.46
20 123.45 to 1 = 123
21 123.45 to 0.2 = 123.4
22 123.457 to 0.5 = 123.5
23 123457 to 10 = 123460
24 123457 to 100 = 123500
25 123457 to 1000 = 123000
26 10.502 to 1 = 11
27 1.15 to 0.1 = 1.2
28 1.150 to 0.1 = 1.2
29 1.050 to 0.1 = 1.0
30 1.051 to 0.1 = 1.1
31 0.350 to 0.1 = 0.4
32 1.251 to 0.1 = 1.3
33 1.252 to 0.1 = 1.3
2 0.350 to 0.1 = 0.4
3 1.050 to 0.1 = 1.0
4 12.999345 to 0.01 = 13.00
5 5 to 0.001 = 5.000
6 0.0325 to 0.001 = 0.032
7 -930 to 20 = -920
8 842 to 20 = 840
9 830 to 20 = 840
10 60.25 to 0.5 = 60.0
11 60.38 to 0.5 = 60.5
12 -60.75 to 0.5 = 61.0
13 0.777777777 to 0.1 = 0.8
14 0.66666666 to 0.1 = 0.7
15 0.333333333 to 0.1 = 0.3
16 0.333333333 to 0.01 = 0.33
17 0.623333 to 0.01 = 0.62
18 123.465 to 0.1 = 123.5
19 123.457 to 0.01 = 123.46
20 123.45 to 1 = 123
21 123.45 to 0.2 = 123.4
22 123.457 to 0.5 = 123.5
23 123457 to 10 = 123460
24 123457 to 100 = 123500
25 123457 to 1000 = 123000
26 10.502 to 1 = 11
27 1.15 to 0.1 = 1.2
28 1.150 to 0.1 = 1.2
29 1.050 to 0.1 = 1.0
30 1.051 to 0.1 = 1.1
31 0.350 to 0.1 = 0.4
32 1.251 to 0.1 = 1.3
33 1.252 to 0.1 = 1.3
再来一个VBA 版本的这个用在EXCEL中
用于EXCEL VBA中的修约
1 ' 参数 dData ：要修约的数
2 ' 参数 dPrecision ：修约间隔默认为1修约到个位，一般为整数或小数 10,100,0.1,0.2,0.5 该值不能为0，
3 ' 若此参数如为空，则用根据dValidDigits参数按有效位进行修约
4 ' 参数 dValidDigits ：有效位，当dPrecision值为“”时，此参数才有效
5 ' 返回值：返回修约后的字符串
6 Public Function DataTrim(dData, Optional ByVal dPrecision As String, Optional ByVal dValidDigits As Integer) As String
7 Dim dblValue As Double, dblPrecision As Double, iDigits As Integer, errValue
8
9 errValue = getNull()
10 If IsError(dData) Then '当值非法时，直接取给定的默认值
11 DataTrim = errValue
12 Exit Function
13 End If
14 If Len(dData) = 0 Or Not IsNumeric(dData) Then
15 DataTrim = errValue '当传入的参数长度为0或者不是数字，则返回空串
16 Exit Function
17 Else
18 dData = CStr(Round(CDbl(dData), 10)) 'Add by Alex(1.2.6, 2010-2-2)
19 End If
20
21 If dPrecision = "" Then
22 '有效位
23 If dValidDigits < 0 Then
24 DataTrim = "有效位错误" '检查修约间隔的合法性
25 Else
26
27 DataTrim = DataValidDigits(CDbl(dData), dValidDigits)
28 End If
29 ElseIf Not IsNumeric(dPrecision) Then
30 DataTrim = "修约间隔格式错误" '检查修约间隔的合法性
31 Else
32 dblPrecision = CDbl(dPrecision)
33 iDigits = DataDecimalDigits(dblPrecision)
34 If iDigits < 0 Then
35 DataTrim = "修约间隔错误" '检查修约间隔的合法性
36 Else
37 dblPrecision = CDbl(dPrecision)
38 dblValue = Round(CDbl(CStr(CDbl(dData) / dblPrecision))) * dblPrecision 'updage by zl(1.2.6, 2010-2-2)
39 If iDigits = 0 Then
40 DataTrim = Format(dblValue, "0")
41 Else
42 DataTrim = Format(dblValue, "0." & Replace(Space(iDigits), " ", "0"))
43 End If
44 End If
45 End If
46 End Function
47
48 ' 修约函数(根据有效位进行修约)
49 ' 参数 dData ：要修约的数
50 ' 参数 dDigits ：有效位数
51 Private Function DataValidDigits(dData As Double, iDigits As Integer) As String
52 Dim dblData As Double, strValue As String, tmpPos As String, i As Integer, j As Integer
53 Dim strData As String, intX As Integer
54 On Error Resume Next
55 If iDigits <= 0 Then
56 strValue = ""
57 Else
58 dblData = Abs(dData)
59 If dblData < 1 Then
60 j = 0
61 '找出小数点后0的个数
62 strData = CStr(dblData)
63 If InStr(1, strData, "E") <= 0 Then
64 strData = Replace(Replace(strData, "0.", ""), ".", "")
65 For i = 1 To Len(CStr(strData))
66 tmpPos = Mid(CStr(strData), i, 1)
67 If CInt(tmpPos) = 0 Then
68 j = j + 1
69 Else
70 Exit For
71 End If
72 Next
73 Else '科学计数法
74 j = CInt(Replace(Replace(Mid(strData, InStr(1, strData, "E") + 1), "+", ""), "-", "")) - 1
75 End If
76
77 iDigits = iDigits + j
78 strValue = Format(dblData, "0." & Replace(Space(iDigits), " ", "0"))
79 '处理后0的个数
80 strData = Replace(Replace(strValue, "0.", ""), ".", "")
81 For i = 1 To Len(CStr(strData))
82 tmpPos = Mid(CStr(strData), i, 1)
83 If CInt(tmpPos) = 0 Then
84 intX = intX + 1
85 Else
86 Exit For
87 End If
88 Next
89 If intX < j Then '如果已经进位
90 strValue = Format(dblData, "0." & Replace(Space(iDigits - 1), " ", "0"))
91 End If
92 Else
93 j = Int(Log(dblData) / Log(10) + 1) '整数位
94 If j < iDigits Then
95 strValue = Format(dblData, "0." & Replace(Space(iDigits - j), " ", "0"))
96 ElseIf j = iDigits Then
97 strValue = Format(dblData, "0")
98 Else
99 strValue = Round(dData / (10 ^ (j - iDigits)), 0) & Replace(Space(j - iDigits), " ", "0")
100 End If
101 End If
102 '若有小数, 检查是否首位进一
103 If InStr(strValue, ".") > 0 Then
104 If CDbl(strValue) < 1 Then
105 If Len(strValue) > iDigits + 2 Then strValue = Left(strValue, iDigits + 2)
106 Else
107 If Len(strValue) > iDigits + 1 Then strValue = Left(strValue, iDigits + 1)
108 End If
109 If Right(strValue, 1) = "." Then strValue = Left(strValue, iDigits)
110 End If
111 If dData < 0 And CDbl(strValue) > 0 Then
112 strValue = "-" & strValue
113 End If
114 End If
115 DataValidDigits = strValue
116 End Function
117
118 ' 检查修约间隔的合法性 返回值 true or false
119 ' 参数 dPrecision 修约间隔指示值，该值不能为空，若为空则返回false
120 ' 返回小位数数位, 若无效, 则返回-1
121 Private Function DataDecimalDigits(ByVal dblPrecision As Double) As Integer
122 Dim dblCheck As Single, dblLog10 As Double, bCheck As Boolean
123 On Error Resume Next
124 If dblPrecision <= 0 Then
125 DataDecimalDigits = -1
126 Else
127 dblLog10 = Log(10)
128 dblCheck = Log(dblPrecision) / dblLog10
129 If dblCheck = Int(dblCheck) Then
130 bCheck = True
131 Else
132 dblCheck = Log(dblPrecision * 5) / dblLog10
133 If dblCheck = Int(dblCheck) Then
134 bCheck = True
135 Else
136 dblCheck = Log(dblPrecision * 2) / dblLog10
137 If dblCheck = Int(dblCheck) Then bCheck = True
138 End If
139 dblCheck = dblCheck - 1
140 End If
141 If bCheck = False Then
142 dblCheck = -1
143 Else
144 If dblCheck >= 0 Then
145 dblCheck = 0
146 Else
147 dblCheck = 0 - dblCheck
148 End If
149 End If
150 DataDecimalDigits = dblCheck
151 End If
152 End Function
153
2 ' 参数 dPrecision ：修约间隔默认为1修约到个位，一般为整数或小数 10,100,0.1,0.2,0.5 该值不能为0，
3 ' 若此参数如为空，则用根据dValidDigits参数按有效位进行修约
4 ' 参数 dValidDigits ：有效位，当dPrecision值为“”时，此参数才有效
5 ' 返回值：返回修约后的字符串
6 Public Function DataTrim(dData, Optional ByVal dPrecision As String, Optional ByVal dValidDigits As Integer) As String
7 Dim dblValue As Double, dblPrecision As Double, iDigits As Integer, errValue
8
9 errValue = getNull()
10 If IsError(dData) Then '当值非法时，直接取给定的默认值
11 DataTrim = errValue
12 Exit Function
13 End If
14 If Len(dData) = 0 Or Not IsNumeric(dData) Then
15 DataTrim = errValue '当传入的参数长度为0或者不是数字，则返回空串
16 Exit Function
17 Else
18 dData = CStr(Round(CDbl(dData), 10)) 'Add by Alex(1.2.6, 2010-2-2)
19 End If
20
21 If dPrecision = "" Then
22 '有效位
23 If dValidDigits < 0 Then
24 DataTrim = "有效位错误" '检查修约间隔的合法性
25 Else
26
27 DataTrim = DataValidDigits(CDbl(dData), dValidDigits)
28 End If
29 ElseIf Not IsNumeric(dPrecision) Then
30 DataTrim = "修约间隔格式错误" '检查修约间隔的合法性
31 Else
32 dblPrecision = CDbl(dPrecision)
33 iDigits = DataDecimalDigits(dblPrecision)
34 If iDigits < 0 Then
35 DataTrim = "修约间隔错误" '检查修约间隔的合法性
36 Else
37 dblPrecision = CDbl(dPrecision)
38 dblValue = Round(CDbl(CStr(CDbl(dData) / dblPrecision))) * dblPrecision 'updage by zl(1.2.6, 2010-2-2)
39 If iDigits = 0 Then
40 DataTrim = Format(dblValue, "0")
41 Else
42 DataTrim = Format(dblValue, "0." & Replace(Space(iDigits), " ", "0"))
43 End If
44 End If
45 End If
46 End Function
47
48 ' 修约函数(根据有效位进行修约)
49 ' 参数 dData ：要修约的数
50 ' 参数 dDigits ：有效位数
51 Private Function DataValidDigits(dData As Double, iDigits As Integer) As String
52 Dim dblData As Double, strValue As String, tmpPos As String, i As Integer, j As Integer
53 Dim strData As String, intX As Integer
54 On Error Resume Next
55 If iDigits <= 0 Then
56 strValue = ""
57 Else
58 dblData = Abs(dData)
59 If dblData < 1 Then
60 j = 0
61 '找出小数点后0的个数
62 strData = CStr(dblData)
63 If InStr(1, strData, "E") <= 0 Then
64 strData = Replace(Replace(strData, "0.", ""), ".", "")
65 For i = 1 To Len(CStr(strData))
66 tmpPos = Mid(CStr(strData), i, 1)
67 If CInt(tmpPos) = 0 Then
68 j = j + 1
69 Else
70 Exit For
71 End If
72 Next
73 Else '科学计数法
74 j = CInt(Replace(Replace(Mid(strData, InStr(1, strData, "E") + 1), "+", ""), "-", "")) - 1
75 End If
76
77 iDigits = iDigits + j
78 strValue = Format(dblData, "0." & Replace(Space(iDigits), " ", "0"))
79 '处理后0的个数
80 strData = Replace(Replace(strValue, "0.", ""), ".", "")
81 For i = 1 To Len(CStr(strData))
82 tmpPos = Mid(CStr(strData), i, 1)
83 If CInt(tmpPos) = 0 Then
84 intX = intX + 1
85 Else
86 Exit For
87 End If
88 Next
89 If intX < j Then '如果已经进位
90 strValue = Format(dblData, "0." & Replace(Space(iDigits - 1), " ", "0"))
91 End If
92 Else
93 j = Int(Log(dblData) / Log(10) + 1) '整数位
94 If j < iDigits Then
95 strValue = Format(dblData, "0." & Replace(Space(iDigits - j), " ", "0"))
96 ElseIf j = iDigits Then
97 strValue = Format(dblData, "0")
98 Else
99 strValue = Round(dData / (10 ^ (j - iDigits)), 0) & Replace(Space(j - iDigits), " ", "0")
100 End If
101 End If
102 '若有小数, 检查是否首位进一
103 If InStr(strValue, ".") > 0 Then
104 If CDbl(strValue) < 1 Then
105 If Len(strValue) > iDigits + 2 Then strValue = Left(strValue, iDigits + 2)
106 Else
107 If Len(strValue) > iDigits + 1 Then strValue = Left(strValue, iDigits + 1)
108 End If
109 If Right(strValue, 1) = "." Then strValue = Left(strValue, iDigits)
110 End If
111 If dData < 0 And CDbl(strValue) > 0 Then
112 strValue = "-" & strValue
113 End If
114 End If
115 DataValidDigits = strValue
116 End Function
117
118 ' 检查修约间隔的合法性 返回值 true or false
119 ' 参数 dPrecision 修约间隔指示值，该值不能为空，若为空则返回false
120 ' 返回小位数数位, 若无效, 则返回-1
121 Private Function DataDecimalDigits(ByVal dblPrecision As Double) As Integer
122 Dim dblCheck As Single, dblLog10 As Double, bCheck As Boolean
123 On Error Resume Next
124 If dblPrecision <= 0 Then
125 DataDecimalDigits = -1
126 Else
127 dblLog10 = Log(10)
128 dblCheck = Log(dblPrecision) / dblLog10
129 If dblCheck = Int(dblCheck) Then
130 bCheck = True
131 Else
132 dblCheck = Log(dblPrecision * 5) / dblLog10
133 If dblCheck = Int(dblCheck) Then
134 bCheck = True
135 Else
136 dblCheck = Log(dblPrecision * 2) / dblLog10
137 If dblCheck = Int(dblCheck) Then bCheck = True
138 End If
139 dblCheck = dblCheck - 1
140 End If
141 If bCheck = False Then
142 dblCheck = -1
143 Else
144 If dblCheck >= 0 Then
145 dblCheck = 0
146 Else
147 dblCheck = 0 - dblCheck
148 End If
149 End If
150 DataDecimalDigits = dblCheck
151 End If
152 End Function
153
最后再来一个SQL版本
sql版的修约函数
1 -- =============================================
2 -- Description: 数值修约 根据中华人民共和国数据修约规则GB8170-87
3 -- 精度范围: 38位长度, 18位小数; 数据有效位为15位
4 -- =============================================
5 CREATE FUNCTION [dbo].[ DataTrim]
6 (
7 @Value as varchar(50),--需要修约的值
8 @Trim as varchar(50) --修约间隔, 即输出值为 @Trim 的倍数, 为 1(或2, 或5) x 10 的 N(整数) 次方, 0 则自动视为 1
9 )
10 RETURNS varchar(50) AS
11 BEGIN
12 -- 声明变量
13 declare @RealValue float(38); --用于保存需要修约的值(dec 型)
14 declare @TempChar varchar(50); --临时值, 用于检查修约间隔的合法性, @TempChar 值为 1 2 5 为合法
15 declare @TempDec float(38); --临时值, 用于四舍六入五考虑
16 declare @TrimValue float(38); --修约间隔
17 declare @Rev varchar(50) --修约返回值（结果）
18 declare @Divisor smallint --修约间隔分别为1(0.2, 0.5) x 10的 N 次方时, @Divisor 值为分别 1 (5, 2)
19 declare @Power smallint --修约间隔的幂数, 即1 (0.2, 0.5) x 10的 N 次方中的 N
20 declare @Negative bit --@Negative 为 1 时, 需要修约的值为负数
21
22
23 if isnumeric(@Value)=0 or @Value is null or isnumeric(@Trim)=0 or @Trim is null
24 set @Rev = ''
25 else
26 begin
27 set @TrimValue = convert(float(38), @Trim)
28
29 if @TrimValue = 0
30 begin
31 set @Trim = '1'
32 set @TempChar = '1'
33 set @TrimValue = 1
34 end
35 else
36 --检查修约间隔的合法性
37 begin
38 set @TempChar = convert(varchar(50), @TrimValue)
39 if @TempChar like '1e%'
40 set @TempChar = '1'
41 else if @TempChar like '2e%'
42 set @TempChar = '2'
43 else if @TempChar like '5e%'
44 set @TempChar = '5'
45 else
46 set @TempChar = replace(replace(@TempChar, '0', ''), '.', '')
47 end
48 if @TempChar in ('1', '2', '5') --修约间隔合法时, 即修约间隔为 1 (0.2, 0.5) x 10的 N 次方
49 begin
50 set @RealValue = convert(float(38), @Value)
51
52 if @RealValue < 0
53 begin
54 set @Negative = 1
55 set @RealValue = abs(@RealValue)
56 end
57 else
58 set @Negative = 0
59
60 if @TempChar = '1'
61 set @Divisor = 1
62 else if @TempChar = '2'
63 set @Divisor = 5
64 else if @TempChar = '5'
65 set @Divisor = 2
66
67 -- 根据修约标准, 修约间隔为 2(5) x 10 的 N 次时, 值和修约间隔乘 5(2) 后进行修约, 再把结果 除以 5(2)
68 set @RealValue = @RealValue * @Divisor
69 set @TrimValue = @TrimValue * @Divisor
70 set @RealValue = @RealValue / @TrimValue
71 set @TempDec = @RealValue - convert(bigint, @RealValue)
72 if @TempDec = 0.5
73 begin
74 -- 个位是奇数(1,3,5,7,9)为入, 反之为舍
75 set @TempDec = convert(bigint, @RealValue) - 10 * (convert(bigint, @RealValue / 10))
76 if @TempDec in (1,3,5,7,9)
77 set @RealValue = convert(bigint, @RealValue) + 1
78 else
79 set @RealValue = convert(bigint, @RealValue)
80 end
81 else if @TempDec > 0.5
82 set @RealValue = convert(bigint, @RealValue) + 1
83 else
84 set @RealValue = convert(bigint, @RealValue)
85 -- 将结果保存以标准的显示方式(不为科学记数法)到@Rev, 保留18位小数, 小数位置改变, 后面的代码作相应的调整
86 set @Rev = convert(varchar(200), convert(dec(38,18), convert(dec(38,0), @RealValue) / @Divisor * @TrimValue))
87 set @Power = cast(log10(@TrimValue) as smallint)
88 if @Divisor <> 1 set @Power = @Power - 1
89 if @Power >= 0
90 set @Rev = left(@Rev, len(@Rev) - 18 - 1) --18为@Rev显示的18位小数, 再去除小数点
91 else
92 set @Rev = left(@Rev, len(@Rev) - 18 - @Power) --18为@Rev显示的18位小数
93 if @Negative = 1 set @Rev = '-' + @Rev
94 end
95 else
96 set @Rev = ''
97 end
98 return @Rev
99
100 END
101
2 -- Description: 数值修约 根据中华人民共和国数据修约规则GB8170-87
3 -- 精度范围: 38位长度, 18位小数; 数据有效位为15位
4 -- =============================================
5 CREATE FUNCTION [dbo].[ DataTrim]
6 (
7 @Value as varchar(50),--需要修约的值
8 @Trim as varchar(50) --修约间隔, 即输出值为 @Trim 的倍数, 为 1(或2, 或5) x 10 的 N(整数) 次方, 0 则自动视为 1
9 )
10 RETURNS varchar(50) AS
11 BEGIN
12 -- 声明变量
13 declare @RealValue float(38); --用于保存需要修约的值(dec 型)
14 declare @TempChar varchar(50); --临时值, 用于检查修约间隔的合法性, @TempChar 值为 1 2 5 为合法
15 declare @TempDec float(38); --临时值, 用于四舍六入五考虑
16 declare @TrimValue float(38); --修约间隔
17 declare @Rev varchar(50) --修约返回值（结果）
18 declare @Divisor smallint --修约间隔分别为1(0.2, 0.5) x 10的 N 次方时, @Divisor 值为分别 1 (5, 2)
19 declare @Power smallint --修约间隔的幂数, 即1 (0.2, 0.5) x 10的 N 次方中的 N
20 declare @Negative bit --@Negative 为 1 时, 需要修约的值为负数
21
22
23 if isnumeric(@Value)=0 or @Value is null or isnumeric(@Trim)=0 or @Trim is null
24 set @Rev = ''
25 else
26 begin
27 set @TrimValue = convert(float(38), @Trim)
28
29 if @TrimValue = 0
30 begin
31 set @Trim = '1'
32 set @TempChar = '1'
33 set @TrimValue = 1
34 end
35 else
36 --检查修约间隔的合法性
37 begin
38 set @TempChar = convert(varchar(50), @TrimValue)
39 if @TempChar like '1e%'
40 set @TempChar = '1'
41 else if @TempChar like '2e%'
42 set @TempChar = '2'
43 else if @TempChar like '5e%'
44 set @TempChar = '5'
45 else
46 set @TempChar = replace(replace(@TempChar, '0', ''), '.', '')
47 end
48 if @TempChar in ('1', '2', '5') --修约间隔合法时, 即修约间隔为 1 (0.2, 0.5) x 10的 N 次方
49 begin
50 set @RealValue = convert(float(38), @Value)
51
52 if @RealValue < 0
53 begin
54 set @Negative = 1
55 set @RealValue = abs(@RealValue)
56 end
57 else
58 set @Negative = 0
59
60 if @TempChar = '1'
61 set @Divisor = 1
62 else if @TempChar = '2'
63 set @Divisor = 5
64 else if @TempChar = '5'
65 set @Divisor = 2
66
67 -- 根据修约标准, 修约间隔为 2(5) x 10 的 N 次时, 值和修约间隔乘 5(2) 后进行修约, 再把结果 除以 5(2)
68 set @RealValue = @RealValue * @Divisor
69 set @TrimValue = @TrimValue * @Divisor
70 set @RealValue = @RealValue / @TrimValue
71 set @TempDec = @RealValue - convert(bigint, @RealValue)
72 if @TempDec = 0.5
73 begin
74 -- 个位是奇数(1,3,5,7,9)为入, 反之为舍
75 set @TempDec = convert(bigint, @RealValue) - 10 * (convert(bigint, @RealValue / 10))
76 if @TempDec in (1,3,5,7,9)
77 set @RealValue = convert(bigint, @RealValue) + 1
78 else
79 set @RealValue = convert(bigint, @RealValue)
80 end
81 else if @TempDec > 0.5
82 set @RealValue = convert(bigint, @RealValue) + 1
83 else
84 set @RealValue = convert(bigint, @RealValue)
85 -- 将结果保存以标准的显示方式(不为科学记数法)到@Rev, 保留18位小数, 小数位置改变, 后面的代码作相应的调整
86 set @Rev = convert(varchar(200), convert(dec(38,18), convert(dec(38,0), @RealValue) / @Divisor * @TrimValue))
87 set @Power = cast(log10(@TrimValue) as smallint)
88 if @Divisor <> 1 set @Power = @Power - 1
89 if @Power >= 0
90 set @Rev = left(@Rev, len(@Rev) - 18 - 1) --18为@Rev显示的18位小数, 再去除小数点
91 else
92 set @Rev = left(@Rev, len(@Rev) - 18 - @Power) --18为@Rev显示的18位小数
93 if @Negative = 1 set @Rev = '-' + @Rev
94 end
95 else
96 set @Rev = ''
97 end
98 return @Rev
99
100 END
101
