Mysql sql 实现两行数据相减的sql写法

huashan8

　　须具备的数据库脚本：

CREATE TABLE `NewTable` (
`id`  bigint(20) NOT NULL AUTO_INCREMENT COMMENT '流水ID数据库自增字段' ,
`queue_name`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '队列名称' ,
`tenant_id`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '租户ID' ,
`user_id`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户ID' ,
`product_total_count`  bigint(20) NOT NULL COMMENT '生产消息总数' ,
`consume_total_count`  bigint(20) NOT NULL COMMENT '消费消息总数' ,
`consumer_count`  bigint(20) NOT NULL COMMENT '消费者数量' ,
`statistic_time`  datetime NOT NULL COMMENT '统计时间' ,
`create_time`  datetime NOT NULL COMMENT '记录创建时间' ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4
ROW_FORMAT=COMPACT
;
两行相减的查询语句:
select
t.id,
t.queue_name,
IFNULL(t.product_total_count - (select product_total_count from mq_rpt_queue_by_hour where id = t.id-1),0),
IFNULL(t.consume_total_count - (select consume_total_count from mq_rpt_queue_by_hour where id = t.id-1),0),
now(),
now(),
t.product_total_count - t.consume_total_count,
IFNULL((t.product_total_count - t.consume_total_count) - (select product_total_count - consume_total_count from mq_rpt_queue_by_hour where id = t.id-1),0),
t.consumer_count
from mq_rpt_queue_by_hour t;

 
当你创建数据库脚本，并且初始化了数据，那么执行下面的SQL查询脚本就可以得出两行相减的结果了。