mysql数据format格式化错误

来源：恒创科技编辑：恒创科技编辑部

2023-12-20 20:55:59

DROP TABLE IF EXISTS `api_billing`;
CREATE TABLE `api_billing` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sequence` varchar(255) DEFAULT NULL,
  `channel` tinyint(4) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `money` decimal(20,15) DEFAULT NULL,
  `balance` float(20,15) DEFAULT NULL,
  `transaction_id` varchar(200) DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of api_billing
-- ----------------------------
INSERT INTO `api_billing` VALUES ('1', '201910101213123123', null, '1', '45.000000000000000', '65.000000000000000', null, '测试', '2019-09-24 16:00:52');
INSERT INTO `api_billing` VALUES ('2', '201910101321231231', '0', '2', '43.000000000000000', '232.000000000000000', null, '微信充值100元', '2019-09-08 16:01:28');
INSERT INTO `api_billing` VALUES ('3', '201910121231222423', '1', '2', '52.700000762939450', '75.000000000000000', null, '测试1', '2019-08-26 16:55:12');
INSERT INTO `api_billing` VALUES ('4', '201910141231221231', '2', '1', '-63.419998168945310', '1.580000042915344', null, '测试2', '2019-09-27 16:55:16');
INSERT INTO `api_billing` VALUES ('5', '201910141231223123', '0', '2', '93.000000000000000', '44.000000000000000', null, null, '2019-02-04 16:55:25');
INSERT INTO `api_billing` VALUES ('6', '201910151231232012', null, '1', '1312.000000000000000', '47.000000000000000', null, null, '2019-06-30 16:55:35');
INSERT INTO `api_billing` VALUES ('7', '201910161231132013', '1', '2', '96.000000000000000', '51.000000000000000', null, '退回尾款', '2019-02-27 16:55:47');
INSERT INTO `api_billing` VALUES ('8', '201910161231232017', null, '4', '23432.000000000000000', '23.000000000000000', null, '测试3', '2019-08-21 16:55:56');
INSERT INTO `api_billing` VALUES ('9', '201910161231231218', '1', '3', '121.000000000000000', '41.000000000000000', null, null, '2019-09-01 16:56:05');
INSERT INTO `api_billing` VALUES ('10', '201910162131232019', '2', '2', '-45.000000000000000', '44.000000000000000', null, '订单', '2019-09-09 16:56:14');
INSERT INTO `api_billing` VALUES ('11', '201910170215478213', null, '1', '-65.120002746582030', '52.000000000000000', null, null, '2019-09-02 16:58:29');
INSERT INTO `api_billing` VALUES ('12', '201910182541257484', '1', '3', '76.250000000000000', '43.000000000000000', null, null, '2019-09-23 16:58:48');
INSERT INTO `api_billing` VALUES ('22', '201910211346456576', '1', '1', '12121.000000000000000', '12273.370117187500000', 'alipay126', '充值', '2019-10-21 13:46:46');

错误的语句

select
            (
              case
                  when money > 0 then
                    CONCAT('收入',abs(format(money, 2)),'元')
                  else
                    CONCAT('支持',abs(format(money, 2)),'元')
              end
            ) as moneyLabel,
            b.*
         from api_billing b where 1=1
       
            and user_id = 1
      
        order by id desc

错误的结果：

mysql数据format格式化错误_微信

正确的写法：

select
        (
            case
                    when money > 0 then
                        CONCAT('收入',abs(truncate(money, 2)),'元')
                    else
                        CONCAT('支持',abs(truncate(money, 2)),'元')
            end
        ) as moneyLabel,
        b.*
 from api_billing b where 1=1

        and user_id = 1

order by id desc

正确的结果

mysql数据format格式化错误_微信_02