MySQL floor()函数使用方法

mysql>  create procedure tax_wages(in s_id smallint,in s_wages smallint)
    ->  begin
    ->  declare c int;
    ->  declare tax int;
    ->  set c=s_wages/1000;
    ->  case c
    ->  when 0 then update tax_table set tax=0,wages=s_wages
    ->  where id=s_id;
    ->  when 1 then update tax_table set tax=(s_wages-1000)*0.02,wages=s_wages
    ->  where id=s_id;
    ->  when 2 then update tax_table set tax=(s_wages-2000)*0.04+20,wages=s_wage
    ->
    ->  where id=s_id;
    ->  else update tax_table set tax=(s_wages-3000)*0.1+60,wages=s_wages
    ->  where id=s_id;
    ->  end case;
    ->  end;//
Query OK, 0 rows affected (0.00 sec)
mysql> call tax_wages(1,2500);//
Query OK, 1 row affected (0.00 sec)
mysql> select * from tax_table;//
+----+------+-------+------+
| id | name | wages | tax  |
+----+------+-------+------+
|  1 | 陈一 |  2500 |   10 |
|  2 | 林二 |     0 |    0 |
|  3 | 张三 |     0 |    0 |
|  4 | 李四 |     0 |    0 |
|  5 | 王五 |     0 |    0 |
+----+------+-------+------+
5 rows in set (0.00 sec)
mysql> drop procedure tax_wages;//
Query OK, 0 rows affected (0.00 sec)
mysql>  create procedure tax_wages(in s_id smallint,in s_wages smallint)
    ->  begin
    ->  declare c int;
    ->  set c=floor(s_wages/1000);
    ->  case c
    ->  when 0 then update tax_table set tax=0,wages=s_wages
    ->  where id=s_id;
    ->  when 1 then update tax_table set tax=(s_wages-1000)*0.02,wages=s_wages
    ->  where id=s_id;
    ->  when 2 then update tax_table set tax=(s_wages-2000)*0.04+20,wages=s_wage
    ->  where id=s_id;
    ->  else update tax_table set tax=(s_wages-3000)*0.1+60,wages=s_wages
    ->  where id=s_id;
    ->  end case;
    ->  end;//
Query OK, 0 rows affected (0.00 sec)
mysql> call tax_wages(1,1500);//
Query OK, 1 row affected (0.00 sec)
mysql> select * from tax_table;//
+----+------+-------+------+
| id | name | wages | tax  |
+----+------+-------+------+
|  1 | 陈一 |  1500 |   10 |
|  2 | 林二 |     0 |    0 |
|  3 | 张三 |     0 |    0 |
|  4 | 李四 |     0 |    0 |
|  5 | 王五 |     0 |    0 |
+----+------+-------+------+
5 rows in set (0.01 sec)
mysql> call tax_wages(2,3000);//
Query OK, 1 row affected (0.00 sec)
mysql> call tax_wages(3,1000);//
Query OK, 1 row affected (0.02 sec)
mysql> call tax_wages(4,5000);//
Query OK, 1 row affected (0.00 sec)
mysql> select * from tax_table;//
+----+------+-------+------+
| id | name | wages | tax  |
+----+------+-------+------+
|  1 | 陈一 |  1500 |   10 |
|  2 | 林二 |  3000 |   60 |
|  3 | 张三 |  1000 |    0 |
|  4 | 李四 |  5000 |  260 |
|  5 | 王五 |     0 |    0 |
+----+------+-------+------+
5 rows in set (0.00 sec)

声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。