当我查询收缩压和舒张压的最大最小值的时候，我发现查询出的最大最小值并不是实际的数值，于是我去看了下表设计，原来字段value的类型是varchar型的，所以sql应该如下ORDER BY value+0 asc
SELECT
id,customer_id,metric,value
FROM
customer_body_metrics_2
WHERE
(
metric = "diastolicPressure"
AND customer_id = "1559017565590265856"
)
ORDER BY value+0 asc
换成mybatis-plus就修改成下面的版本了
@Override
@UseDataSource(type = DataSourceTypeEnum.SHARDING_DATASOURCE)
public Double getMax(String customerId, LocalDateTime firstDay, LocalDateTime lastDay, String metric) {
StopWatch stopWatch = new StopWatch();
stopWatch.start("getMax");
LambdaQueryWrapper<CustomerBodyMetricsEntity> wrapper = this.getWrapper(metric);
wrapper.eq(CustomerBodyMetricsEntity::getCustomerId, customerId)
.ge(CustomerBodyMetricsEntity::getVersion, DateTimeUtil.dateTimeToTimestamp(firstDay))
.le(CustomerBodyMetricsEntity::getVersion, DateTimeUtil.dateTimeToTimestamp(lastDay));
wrapper.apply("ORDER BY value+0 desc");
wrapper.last(DaoConst.SQL_LIMIT_ONE);
CustomerBodyMetricsEntity one = this.getOne(wrapper);
stopWatch.stop();
log.debug("content getMax time{}s",stopWatch.getTotalTimeSeconds());
if (one != null) {
return Double.valueOf(one.getValue());
}
return 0D;
}
但是报错了，逻辑sql是这样的：
SELECT id,customer_id,metric,value,unit,label,proportion,reference,label_ranges,env,version,tag,datasource_id,comment,status,tenant_id,create_time,update_time
FROM customer_body_metrics
WHERE (metric = ? AND customer_id = ? AND version >= ? AND version <= ? AND ORDER BY value+0 desc) limit 1
把sql复制到数据库去运行，果然不一样，查看了下，原来是最后多了个“and”
于是我去查看了下代码，原来是last不会加“and”,apply会加“and”，于是有进行了修改
修改之后的代码
@Override
@UseDataSource(type = DataSourceTypeEnum.SHARDING_DATASOURCE)
public Double getMax(String customerId, LocalDateTime firstDay, LocalDateTime lastDay, String metric) {
StopWatch stopWatch = new StopWatch();
stopWatch.start("getMax");
LambdaQueryWrapper<CustomerBodyMetricsEntity> wrapper = this.getWrapper(metric);
wrapper.eq(CustomerBodyMetricsEntity::getCustomerId, customerId)
.ge(CustomerBodyMetricsEntity::getVersion, DateTimeUtil.dateTimeToTimestamp(firstDay))
.le(CustomerBodyMetricsEntity::getVersion, DateTimeUtil.dateTimeToTimestamp(lastDay));
wrapper.last("ORDER BY value+0 desc"+DaoConst.SQL_LIMIT_ONE);
CustomerBodyMetricsEntity one = this.getOne(wrapper);
stopWatch.stop();
log.debug("content getMax time{}s",stopWatch.getTotalTimeSeconds());
if (one != null) {
return Double.valueOf(one.getValue());
}
return 0D;
}
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/65557.html
