SQL 选择“位置”日期在(1 月和 3 月)，但不在 2 月

Question

我在SQL中有一个名为Balance的表。

    +----+-----------+-------+------+
    | id | accountId | Date  | Type |
    +----+-----------+-------+------+
    | PK | FK        | Date  | Int  |
    +----+-----------+-------+------+

I need to find the accountIds that has balance entries in January and March, but not in Febuary. Only in 2018 and Type should be 2.

How would I go about writing my sql select statement?

Thanks

Edit: What's I've done so far: Selecting rows that either in Jan OR March is not a problem for me.

SELECT AccountId, Date FROM Balance
WHERE Month(Date) in (1,3) AND YEAR(Date) = 2018 AND Type =2
ORDER BY AccountId, Date

但是，如果 AccountId 只有一个条目，例如在 1 月份，那么这将包括在内。这不是我想要的。只有当一个帐户在 1 月和 3 月都有条目，而不是在 2 月时，它才有意义。

我怀疑Group BY和HAVING是这里的关键，但我不确定如何进行

Answer 1

我会使用聚合来做到这一点：

select b.accountid
from balance b
where date >= '2018-01-01' and date < '2019-01-01'
group by b.accountid
having sum(case when month(date) = 1 then 1 else 0 end) > 0 and  -- has january
       sum(case when month(date) = 3 then 1 else 0 end) > 0 and  -- has march
       sum(case when month(date) = 2 then 1 else 0 end) = 0  -- does not have february

Answer 2

您可以使用存在来完全避免分组：

select distinct b.accountid
from balance b
where exists (
        select *
        from balance b1
        where month(b1.date) = 1 and b.accountid = b.accountid
    ) and exists (
        select *
        from balance b1
        where month(b1.date) = 3 and b.accountid = b.accountid
    ) and not exists (
        select *
        from balance b1
        where month(b1.date) = 2 and b.accountid = b.accountid
    )