CREATE TABLE `my_decimal` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`VALUE` decimal(20,3) DEFAULT NULL,
`MARK` varchar(8) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8INSERT INTO `my_decimal`(value,) VALUES (0.120 ,'a');
INSERT INTO `my_decimal`(value,) VALUES (0.500 ,'b');
INSERT INTO `my_decimal`(value,) VALUES (0.550 ,'c');
INSERT INTO `my_decimal`(value,) VALUES (1.000 ,'d');
INSERT INTO `my_decimal`(value,) VALUES (1.150 ,'e');
INSERT INTO `my_decimal`(value,) VALUES (2.300 ,'f');
INSERT INTO `my_decimal`(value,) VALUES (3.200 ,'g');
INSERT INTO `my_decimal`(value,) VALUES (4.700 ,'h');
INSERT INTO `my_decimal`(value,) VALUES (5.580 ,'i');
INSERT INTO `my_decimal`(value,) VALUES (7.590 ,'j');
现在想做一个查询取精度value 和 两个字段合并一起 且 value精度 去掉小数点后面0最后的结果是这样:0.12a
0.5b
0.55c
1d
1.15e
2.3f
3.2g
4.7h
5.58i
7.59j以前的做法是SELECT CONCAT(CAST(VALUE AS DECIMAL(6,2)),) FROM my_decimal0.12a
0.50b
0.55c
1.00d
1.15e
2.30f
3.20g
4.70h
5.58i
7.59j这样弄出来的精度却是这样我不想要的、精度这边查了很多没办法了、
mysql有没有其他函数可以找精度?
`id` int(11) NOT NULL AUTO_INCREMENT,
`VALUE` decimal(20,3) DEFAULT NULL,
`MARK` varchar(8) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8INSERT INTO `my_decimal`(value,) VALUES (0.120 ,'a');
INSERT INTO `my_decimal`(value,) VALUES (0.500 ,'b');
INSERT INTO `my_decimal`(value,) VALUES (0.550 ,'c');
INSERT INTO `my_decimal`(value,) VALUES (1.000 ,'d');
INSERT INTO `my_decimal`(value,) VALUES (1.150 ,'e');
INSERT INTO `my_decimal`(value,) VALUES (2.300 ,'f');
INSERT INTO `my_decimal`(value,) VALUES (3.200 ,'g');
INSERT INTO `my_decimal`(value,) VALUES (4.700 ,'h');
INSERT INTO `my_decimal`(value,) VALUES (5.580 ,'i');
INSERT INTO `my_decimal`(value,) VALUES (7.590 ,'j');
现在想做一个查询取精度value 和 两个字段合并一起 且 value精度 去掉小数点后面0最后的结果是这样:0.12a
0.5b
0.55c
1d
1.15e
2.3f
3.2g
4.7h
5.58i
7.59j以前的做法是SELECT CONCAT(CAST(VALUE AS DECIMAL(6,2)),) FROM my_decimal0.12a
0.50b
0.55c
1.00d
1.15e
2.30f
3.20g
4.70h
5.58i
7.59j这样弄出来的精度却是这样我不想要的、精度这边查了很多没办法了、
mysql有没有其他函数可以找精度?
FROM `my_decimal`
+----+-------+------+
| id | VALUE | MARK |
+----+-------+------+
| 15 | 0.120 | a |
| 16 | 0.500 | b |
| 17 | 0.550 | c |
| 18 | 1.000 | d |
| 19 | 1.150 | e |
| 20 | 2.300 | f |
| 21 | 3.200 | g |
| 22 | 4.700 | h |
| 23 | 5.580 | i |
| 24 | 7.590 | j |
+----+-------+------+
10 rows in set (0.00 sec)mysql> SELECT *,CONCAT(0+(CAST(`VALUE` AS CHAR(4))),`MARK`) AS SS
-> FROM `my_decimal`;
+----+-------+------+-------+
| id | VALUE | MARK | SS |
+----+-------+------+-------+
| 15 | 0.120 | a | 0.12a |
| 16 | 0.500 | b | 0.5b |
| 17 | 0.550 | c | 0.55c |
| 18 | 1.000 | d | 1d |
| 19 | 1.150 | e | 1.15e |
| 20 | 2.300 | f | 2.3f |
| 21 | 3.200 | g | 3.2g |
| 22 | 4.700 | h | 4.7h |
| 23 | 5.580 | i | 5.58i |
| 24 | 7.590 | j | 7.59j |
+----+-------+------+-------+
10 rows in set, 10 warnings (0.00 sec)mysql>
mysql> SELECT CONCAT(SS1, `MARK`) FROM (
-> SELECT *,
-> IF(0+RIGHT(SS,1)='0' AND 0+RIGHT(SS,2)='0',MID(SS,1,INSTR(SS,'.')-1),
-> IF(0+RIGHT(SS,1)='0' AND 0+RIGHT(SS,2)<>'0',MID(SS,1,INSTR(SS,'.')+1),SS)
) AS SS1
->
-> FROM (
-> SELECT *,CAST(`VALUE` AS CHAR(4)) AS SS
-> FROM `my_decimal` ) A) S;
+---------------------+
| CONCAT(SS1, `MARK`) |
+---------------------+
| 0.12a |
| 0.5b |
| 0.55c |
| 1d |
| 1.15e |
| 2.3f |
| 3.2g |
| 4.7h |
| 5.58i |
| 7.59j |
+---------------------+
10 rows in set, 10 warnings (0.00 sec)mysql>
SELECT CONCAT(0+CAST(VALUE AS CHAR),MARK) as `VALUE` FROM my_decimal WHERE `ID` =nn结贴了、