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

须具备的数据库脚本：

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查询脚本就可以得出两行相减的结果了。