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

亦凉 2021-07-26 23:23 361阅读 0赞

### 首先表是这样的 ###

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

### 数据是这样的 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1YW5zag_size_16_color_FFFFFF_t_70][]

## ##

### 视图建立 ###

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;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1YW5zag_size_16_color_FFFFFF_t_70]: /images/20210726/c9edcde81c4d4465961a1d43a147511e.png