现在有一个table.举例如下
ID H W F
1 5
2 9
3 10
4 0
5
现在W的值等于(H的值/8),保留小数点一位F的值等于H大于8的,按照1算。小于8的按照(H/8),保留小数点一位其中H值可能为空或者为0现在用PHP+MYSQL做东西,遇到这个问题。该如何解决?
ID H W F
1 5
2 9
3 10
4 0
5
现在W的值等于(H的值/8),保留小数点一位F的值等于H大于8的,按照1算。小于8的按照(H/8),保留小数点一位其中H值可能为空或者为0现在用PHP+MYSQL做东西,遇到这个问题。该如何解决?
W = TRUNCATE(NULLIF(H,0)/8,1),
F = TRUNCATE(IF(NULLIF(H,0)>8,1,NULLIF(H,0)/8),1)
表的结果应该是这样的:ID H W F
1 5 0.63 0.63
2 9 1.13 1
3 10 1.25 1
4 0 0 0
5
set W=round(H/8,2),
F=round(if(H>8,8,H)/8,2)
表的结果应该是这样的:ID H W F
1 5 0.63 0.63
2 9 1.13 1
3 10 1.25 1
4 0 0 0
5
set f=(if(w<=1 or w is null,w,1)
+----+------+------+------+
| id | h | w | f |
+----+------+------+------+
| 1 | 5 | NULL | NULL |
| 2 | 9 | NULL | NULL |
| 3 | 10 | NULL | NULL |
| 4 | 0 | NULL | NULL |
| 5 | NULL | NULL | NULL |
+----+------+------+------+
5 rows in set (0.00 sec)mysql> update table1
-> set W=round(H/8,2),
-> F=round(if(H>8,8,H)/8,2);
Query OK, 4 rows affected (0.06 sec)
Rows matched: 5 Changed: 4 Warnings: 0mysql> select * from table1;
+----+------+------+------+
| id | h | w | f |
+----+------+------+------+
| 1 | 5 | 0.62 | 0.62 |
| 2 | 9 | 1.12 | 1 |
| 3 | 10 | 1.25 | 1 |
| 4 | 0 | 0 | 0 |
| 5 | NULL | NULL | NULL |
+----+------+------+------+
5 rows in set (0.00 sec)mysql>
set
w=round(h/8,2),
f=if(round(h/8,2)<=1 or round(h/8,2) is null,round(h/8,2),1)