postgresql中檢視建立，欄位拼接，同一個表的多行之間的多個欄位相減

木头左發表於2024-04-26
SQL

首先表是這樣的

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


#### 欄位拼接和檢視建立


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

相關文章