Pgsql 查询某个id下B表中的属性是否存在在A表字段现有属性中（设备）

一.通过id查询B表所有设备去看A表是否有这些设备存在，返回已有最新存在的设备。（举一反三）

二.sql代码

--994140302  ?
SELECT ws.*,s.device FROM B ws LEFT JOIN A s ON ws.aton_id=s.aton_id
 WHERE ws.aton_id=994140302 AND CAST(ws.type AS varchar)=ANY(regexp_split_to_array(s.device, E','))

 

--查询某个id下的数据根据所选时间点的左右五分钟查询单条数据 （查出所属的）

SELECT
	es.station_name,
	MAX ( ec.bearing ) AS bearing,
	MAX ( ec.pitch_angle ) AS pitch_angle,
	MAX ( ec.roll_angle ) AS roll_angle
	FROM tableA es
	LEFT JOIN tableB ec ON ec.aton_id = es.aton_id 
	AND ec.acquisition_timestamp  BETWEEN to_timestamp( ?, 'yyyy-MM-dd hh24:mi:ss' ) + INTERVAL '-3 M' 
	AND to_timestamp( ?, 'yyyy-mm-dd hh24:mi:ss' ) + INTERVAL '+2 M' 
WHERE
	CAST ( es.aton_id AS VARCHAR ) = ANY ( regexp_split_to_array( ?, E',' ) ) 
GROUP BY
	es.station_name

三.Java实现 （jfanal）

 /**
     * 站点对比分页查询
     *
     * @param index Integer 页码
     * @param limit Integer 条数
     * @param ids   String 站点ID 多个 , 分隔
     * @param time  String 时间 yyyy-MM-dd hh24:mm:ss
     * @return Page<Record> 分页数据集合
     */
    public Page<Record> listStationComparedPage(Integer index, Integer limit, String ids, String time) {
        String select = "SELECT es.station_name,ec.*";
        String from = " FROM A es LEFT JOIN B ec ON ec.aton_id=es.aton_id AND ec.acquisition_timestamp " +
                " BETWEEN to_timestamp(?, 'yyyy-MM-dd hh24:mi:ss') + INTERVAL '-3 M' AND to_timestamp(?,'yyyy-mm-dd hh24:mi:ss') + INTERVAL '+2 M' " +
                " WHERE CAST(es.aton_id AS varchar) = ANY(regexp_split_to_array(?, E','))  ";
        Record paras = SqlUtil.assemblyParam(Timestamp.valueOf(time), Timestamp.valueOf(time), ids);
        return Db.use(Constant.ENAVMSI).paginate(index, limit, select, from, paras.getColumnValues());
    }

List  返回 

return dao.find(sql.toString(), atonId);

void 返回

return dao.findFirst(sql.toString(), atonId);