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;`