使用高级查询 ,后端使用sql左连接方式报错

[zhangle1]

前端传参

{"pageIndex":0,"pageSize":12,"sorts":[{"name":"device_state","order":"asc"},{"name":"id","order":"asc"}],"terms":[{"terms":[{"type":"or","value":"2024-06-29 11:22:55","termType":"gt","column":"today_start_timeb"}]}]}

java代码方面

       Mono<PagerResult<DeviceInstanceEntity>> source = queryHelper
            .select("WITH today_device_state AS (\n" +
                        "    SELECT\n" +
                        "        device_code,\n" +
                        "        MIN(start_time) AS today_start_timeb,\n" +
                        "        MAX(start_time) AS today_last_timeb\n" +
                        "    FROM\n" +
                        "        g_device_state\n" +
                        "    WHERE\n" +
                        "        start_time = CURRENT_DATE\n" +
                        "    GROUP BY\n" +
                        "        device_code\n" +
                        ")\n" +
                        "\n" +
                        "SELECT\n" +
                        "    h.*, \n" +
                        "    t.today_start_timeb,\n" +
                        "    t.today_last_timeb\n" +
                        "FROM\n" +
                        "    dev_device_instance h\n" +
                        "left JOIN\n" +
                        "    today_device_state t ON h.id = t.device_code", DeviceInstanceEntity::new)
            .where(query)
            .fetchPaged();

sql

WITH today_device_state AS (
    SELECT
        device_code,
        MIN(start_time) AS startTime,
        MAX(start_time) AS lastTime
    FROM
        g_device_state
    WHERE
        start_time >= CURRENT_DATE
    GROUP BY
        device_code
)

SELECT
    h.*,
    t.startTime,
    t.lastTime
FROM
    dev_device_instance h
JOIN
    today_device_state t ON h.id = t.device_code;

SELECT
    device_code,
    MIN(start_time) FILTER (WHERE start_time >= CURRENT_DATE) AS startTime,
    MAX(start_time) FILTER (WHERE start_time >= CURRENT_DATE) AS lastTime
FROM
    g_device_state

        where start_time >='2024-06-01 11:18:15'
GROUP BY
    device_code;

报错

操作符不存在: timestamp without time zone > character varying

我希望能使用高级查询去对左连接的表进行关联 ,还是我哪里弄错了

[zhou-hao]

日志中打印的sql是什么

[zhou-hao]

start_time 字段类型是什么

[zhangle1]

@Comment("开始时间")
@Column(name = "start_time")
@Schema(description = "开始时间")
private Timestamp start_time;

@Comment("结束时间")
@Column(name = "end_time")
@Schema(description = "结束时间")
private Timestamp end_time;  类型是这个

[zhangle1]

WITH today_device_state AS (SELECT device_code, MIN(start_time) AS "today_start_timeb", MAX(start_time) AS "today_last_timeb" FROM jetlinks.g_device_state WHERE start_time = CURRENT_DATE GROUP BY device_code) SELECT count(1) as _total FROM jetlinks.dev_device_instance h LEFT JOIN today_device_state t ON h.id = t.device_code  WHERE  ( ( t."today_start_timeb" > '2024-06-12 13:09:24' ) )  

这是报错之前控制台输出的最后一个sql io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: 操作符不存在: timestamp without time zone > character varying ,在数据库客户端里这段sql能正常执行

[zhou-hao]

today_start_timeb 呢 ？ 字段类型建议改成 Long 或者 Date试试呢？

[zhangle1]

today_start_timeb 这个数据库里不存在的
with 里的表 对接给前端用的

[zhangle1]

@Comment("开始时间") @Column(name = "start_time") @Schema(description = "开始时间") private Timestamp start_time; 实际的类型就是这个

[zhou-hao]

java里类型改成Long试试