SQL查询未按预期运行（之间） [英] SQL Query doesn't run as intended(between)

问题描述

我有以下查询（在PostgreSQL中）

I have the following query(in postgresql),

select distinct 
    "bookings"."memberId" 
from "shifts" 
inner join "bookings" 
on "bookings"."shiftId" = "shifts"."id" 
where "shifts"."startTime" not between '2016-01-02 00:00:00.000' and '2020-01-01 23:59:59.999' 
    and "shifts"."startTime" >= '2015-01-01 00:00:00.000' 
    and "shifts"."startTime" <= '2016-01-01 23:59:59.999' 
    and "bookings"."state" in ('ACCEPTED')

我试图选择一些不在2016年至2020年之间但在2015年至2016年之间的memberId ，查询仍会返回一个memberId，该成员实际在2016年至2020年之间

I am trying to select some memberId's that aren't between 2016 and 2020 but are between 2015 and 2016, however, the query still returns a memberId which is actually between 2016 and 2020

推荐答案

您似乎想要聚合：

select b."memberId" 
from "shifts" s join
     "bookings" b
     on b."shiftId" = s."id" 
where s."startTime" >= '2015-01-01' and
      b."state" in ('ACCEPTED')
group by b."memberId"
having max(s."startTime") < '2016-01-01' ;

注意：这是根据问题而非您现有的查询使用日期/时间。您可能需要调整它们（我不确定为什么会有1月2日，例如在 WHERE 子句中）。

Note: this uses date/times based on the question rather than in your existing query. You may need to adjust them (I'm not sure why you have January 2nd, for instance in the WHERE clause).

这将获取2015年或之后的所有行，并按会员ID进行汇总。 HAVING 子句仅选择最大日期为2015年的成员。

This takes all rows on or after 2015, and aggregates by the member id. The HAVING clause selects only members whose maximum date is in 2015.

这篇关于SQL查询未按预期运行（之间）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！