时间戳

首页 > 解决方案 > 范围内未找到 H2 SQL 查询列

sql - 范围内未找到 H2 SQL 查询列

问题描述

与：SQL：查找约会中每分钟资源的不可用性

（（（单击上面的链接获取架构+信息））

我正在尝试在 H2 SQL 数据库中运行此查询。我对 H2 语法有点不熟悉。我注意到导致问题的 WHERE 子句中的num列。

错误：

未找到列“NUM”；SQL 语句：CREATE FORCE VIEW ( SELECT "A"."APPOINTMENT_ID", "A"."APPOINTMENT_START_TIME", "A"."APPOINTMENT_END_TIME", "C"."COMPONENT_HOST_NAME", 'unavailable' AS "STATE" FROM "PUBLIC "."APPOINTMENT" "A" LEFT OUTER JOIN "PUBLIC"."APPOINTMENT_COMPONENT" "AC" ON "A"."APPOINTMENT_ID" = "AC"."APPOINTMENT_ID" INNER JOIN "PUBLIC"."COMPONENT" "C" ON "C"."COMPONENT_ID" = "AC"."COMPONENT_ID" WHERE ((CONVERT("A"."APPOINTMENT_START_TIME", TIME) <= DATEADD('minute', "NUM", CAST('00:00:00) '

我的代码：

with times(num) as 
(
  select 30 as num
  union all select (num + 30)
  from times where num < (24*60)
)
select dateadd('minute', num, cast('00:00:00' as time)) as datetimeinterval, unavailabilities.state from times
outer join(
select top 1 a.appointment_id, a.appointment_start_time, a.appointment_end_time, c.component_host_name, 'unavailable' as state
    from  appointment a 
    left join appointment_component ac on a.appointment_id = ac.appointment_id
    inner join component c on c.component_id = ac.component_id
    where
            dateadd('minute', -->num<--, cast('00:00:00' as time)) between convert(a.appointment_start_time, time) and convert(a.appointment_end_time, time)
    and 
        c.component_id in (1)
) unavailabilities

TLDR：尝试按分钟或按分钟范围（此处为 30 分钟）获取组件列表的不可用性。在这种情况下，Num 应该返回 30 的倍数，具体取决于选择的时间范围，它将检查组件是否被采用。

注意我从上面的链接更改了 machine=component 和 appmach=appointment_component （交叉表）

标签： sqlh2

解决方案

我不确定语法。我对H2不是很熟悉。但是不num应该在 on 子句而不是 subquery 中使用吗？请检查：

with recursive times(num) as 
(
  select 30 as num
  union all select (num + 30)
  from times where num < (24*60)
)
select dateadd('minute', num, cast('00:00:00' as time)) as datetimeinterval, unavailabilities.state from times
outer join(
select top 1 a.appointment_id, a.appointment_start_time, a.appointment_end_time, c.component_host_name, 'unavailable' as state
    from  appointment a 
    left join appointment_component ac on a.appointment_id = ac.appointment_id
    inner join component c on c.component_id = ac.component_id
    where
            dateadd('minute', -->num<--, cast('00:00:00' as time)) between convert(a.appointment_start_time, time) and convert(a.appointment_end_time, time)
    and 
        c.component_id in (1)


   ) unavailabilities 
on dateadd('minute', num, cast('00:00:00' as time)) between convert(appointment_start_time, time) and convert(appointment_end_time, time)

推荐阅读