postgresql中视图建立，字段拼接，同一个表的多行之间的多个字段相减

首先表是这样的

CREATE TABLE public.tb_realtime_data (
	s_id varchar(48) NOT NULL,
	sensor_name varchar(48) NULL,
	sensor_index_code varchar(48) NULL,
	sensor_value numeric(20,10) NULL,
	statistics_status int4 NULL,
	alarm_time timestamp NOT NULL,
	create_time timestamp NULL,
	seq int4 NULL, -- 序列
	CONSTRAINT pk_tb_realtime_data PRIMARY KEY (s_id)
);

数据是这样的

 

视图建立

create view v_sensor_realtime_data as select tb_realtime_data.s_id,  tb_realtime_data.sensor_index_code, sensor_value, statistics_status, alarm_time, create_time, seq, sensor_region, tb_sensor.sensor_name
from tb_realtime_data left join tb_sensor on tb_realtime_data.sensor_index_code = tb_sensor.sensor_index_code;

字段拼接和视图建立

drop view v_sensor_name_realtime_data;
create view v_sensor_name_realtime_data as select tb_realtime_data.s_id,  tb_realtime_data.sensor_index_code, sensor_value, statistics_status, alarm_time, create_time, seq, 
sensor_region, tb_sensor.sensor_name,sensor_region || tb_sensor.sensor_name as sensor_region_name
from tb_realtime_data left join tb_sensor on tb_realtime_data.sensor_index_code = tb_sensor.sensor_index_code; ;

sql中同一个表的多行之间的多个字段相减

select a.*,a.statistics_status -b.statistics_status as d_statistics_status, a.sensor_value - b.sensor_value as d_sensor_value
from (select * from tb_realtime_data where  alarm_time='2019-06-12 00:00:00') a,
(select * from tb_realtime_data where  alarm_time='2019-06-12 00:02:00') b  
where a.sensor_index_code = b.sensor_index_code;