SQL function)

A Calculation Method of Analytical Chemistry
Four Homes, less than or equal to four homes,
Six-in-one. If the value is greater than or equal to six, add one.
Five to five pairs. If it is five, the last one is a pair or a singular number.

For an approximate number with a large number of digits, when the number of digits is determined, the extra digits following the valid digits should be removed, and only the last digit of the valid number should be retained. This round (rounding) the rule is "four homes, six homes, five into two ",

That is to say, "four homes, six homes, and five shopping carnivals" here "four" means less than five, and "six" means more than five, "5" is the last digit of the exact bit,

When the number is after 5, the number of homes is 5, and the number of homes after 1, 5 is countless or 0: ① 5 is an odd number before, and 5 is 1; ② 5 is an even number before, and 5 is not in.

The specific rules are as follows:

1. If the value is less than 5, and the value of the portion is smaller than the half unit of the last digit of the portion, the last digit remains unchanged;

2. If the value of the given part is greater than 5 to 1, and the value of the given part is greater than the half unit of the last part of the reserved part, 1 is added to the last part;

3. if the value is equal to 5, an even number is obtained. If the value is equal to the half unit of the last digit of the reserved part, the last digit is an even number. That is, when the last digit is an even number, the last digit remains unchanged; when the last digit is an odd number, add 1 to the last digit.

For example, the preceding rule is used to retain three valid numbers for the following data:

9.8249 = 9.82, 9.82671 = 9.83 9.8251 = 9.83, 9.8350 = 9.84 9.8250 = 9.82, 9.82501 = 9.83

 

The SQL code is from the network and the execution result is incorrect.

1/* use test data 2 1.445 1.44 3 1.435 1.44 4 1.425 1.42 5 1.635 1.64 6 */7 if object_id ('fnround ') is not null 8 Drop function fnround 9 go 10 create function fnround (@ num float, @ I INT) 11 returns varchar (20) 12 as 13 begin 14 declare @ STR varchar (20) /* convert to character type */15 declare @ str2 varchar (20)/* string after decimal places */16 declare @ str3 varchar (2) /* scale string */17 set @ STR = convert (varchar, @ num) 18 set @ str2 = reverse (substring (reverse (@ Str), 1, charindex ('. ', reverse (@ Str)-1-@ I) 19 set @ str3 = substring (@ STR, charindex ('. ', @ Str) + 1, @ I) 20 if @ str2 % 5 = 0 and @ str3 % 2 = 0/* if it meets the "Wucheng dual" */21 set @ STR = substring (@ STR, 1, charindex ('. ', @ Str) + @ i-1) 22 else/* Otherwise rounding */23 set @ STR = convert (varchar, round (@ num, @ I )) 24 return @ STR 25 endgo 26 27 ---- Test 28 declare @ num float, @ num2 float, @ num3 float, @ num4 float, @ I int 29 select @ num = 1.445, @ num2 = 1.435, @ num3 = 1.425, @ num4 = 1.635 30 set @ I = 2/* Reserved decimal places */31 select 32 DBO. fnround (@ num, @ I) as [1.445],/* X double (4 is already an even number and remains unchanged) */33 DBO. fnround (@ num2, @ I) as [1.435],/* dual (Change 3 to an even number 4) */34 DBO. fnround (@ num3, @ I) as [1.425],/* X double (2 is already an even number and remains unchanged) */35 DBO. fnround (@ num4, @ I) as [1.635]/* rounding */36 37 ---- clear test environment 38 drop function fnround 39 40/* Result 41 1.445 1.435 1.425 1.635 42 ---------------------- ---------------- -------------------------- 43 1.44 1.44 1.42 44 */

Content from: csdn

Right and wrong are to be studied ====>>>>>>>>>>>

SQL code right and wrong are to be studied. All the above test data is OK!

1/* use the same test data 2 2 1.445 1.44 3 3 1.435 1.44 4 1.425 1.42 5 5 1.635 1.64 6 */7 if object_id ('looround ') is not null drop function leoround 8 go 9/* 10 * @ num the number of decimal places to be repaired is 11 * @ I the number of decimal places is 12 */13 CREATE FUNCTION leoround (@ num float, @ I INT) 14 returns varchar (20) 15 as16 begin17 declare @ STR varchar (20)/* convert original data to character type */18 declare @ str2 varchar (20) /* retain digits after decimal places-for example, if 1.5401 is reserved for one digit, @ str2 = '000000' */19 declare @ str3 varchar (2) /* The Last reserved decimal point -- for example, if 1.54 is reserved for a single digit, @ str3 = '5' */20 21 set @ STR = convert (varchar, @ num) 22/* if it is an integer, the original value */23 if charindex ('. ', @ Str) = 0 24 return @ str25/* number after the decimal point is retained */26 set @ str2 = reverse (substring (reverse (@ Str), 1, charindex ('. ', reverse (@ Str)-1-@ I) 27/* indicates the last decimal point to be retained. Note that if the number of decimal places to be retained is 0, decimal point truncation problem */28 If @ I = 029 set @ str3 = substring (@ STR, charindex ('. ', @ Str)-1, 1) 30 else31 set @ str3 = substring (@ STR, charindex ('. ', @ Str) + 1, @ I) 32 33/* meets the condition of a x pair */34 if substring (@ str2) % 5 = 035 begin36 if Len (@ str2)> = 2 and substring (@ str2, 2, Len (@ str2)-1)> 0/* determine whether there are digits not 0 after 5 */37/* if there is, then one (note the decimal point truncation problem) */38 If @ I = 039 set @ STR = convert (float, substring (@ STR, 1, charindex ('. ', @ Str)-1 + @ I) + 1.0/power (10, @ I) 40 else41 set @ STR = convert (float, substring (@ STR, 1, charindex ('. ', @ Str) + @ I) + 1.0/power (10, @ I) 42 else43/* does not exist. In addition, you need to keep the parity of the last decimal digit, if @ str3 % 2 = 045/* even number five, note the decimal point truncation problem */46 If @ I = 047 set @ STR = substring (@ STR, 1, charindex ('. ', @ Str)-1 + @ I) 48 else 49 set @ STR = substring (@ STR, 1, charindex ('. ', @ Str) + @ I) 50 else51/* an odd number. You must note the decimal point truncation problem */52 If @ I = 053 set @ STR = convert (float, substring (@ STR, 1, charindex ('. ', @ Str)-1 + @ I) + 1.0/power (10, @ I) 54 else 55 set @ STR = convert (float, substring (@ STR, 1, charindex ('. ', @ Str) + @ I) + 1.0/power (10, @ I) 56 end57/* four homes and six entries */58 else 59 set @ STR = convert (varchar, round (@ STR, @ I) 60 return @ str61 end