仅在特定日期之后选择带有酒吧事件的foo [英] Select foo with bar events only after a specific date

问题描述

考虑两个表:

Foo:
  id INT,
  name VARCHAR

Bar:
  id INT,
  foo_id INT REFERENCES Foo(id),
  event SET('hit','miss'),
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

每个Foo项目可以具有多个Bar事件. 如何查询在特定日期时间后具有 Bar事件但在该日期时间前没有 Bar事件的Foo项目? >

例如，考虑:

Foo id=1:
    hit 2014-01-21
    hit 2014-01-22
    hit 2014-01-23

Foo id=2:
    hit 2014-01-17
    hit 2014-01-19
    hit 2014-01-21

Foo id=3:
    hit 2014-01-14
    hit 2014-01-15
    hit 2014-01-18

Foo id=4:
    hit 2014-01-21
    hit 2014-01-22
    hit 2014-01-24

我只想查询具有2014-01-20或之后的匹配项的Foo项目，因此应该返回ID 1和4.

我尝试使用以下内容的一些变体将Bar表连接到自身:

SELECT
    b1.id
FROM
    Bar b1 JOIN Bar b2 using (foo_id)
WHERE
    b1.ts > '2014-01-20';

但是，我看不到如何用WHERE子句来排除任何日期在2014-01-20之前的组合.

解决方案

SELECT   Foo.*
FROM     Foo JOIN Bar ON Bar.foo_id = Foo.id
GROUP BY Foo.id
HAVING   MIN(Bar.ts) >= '2014-01-20'

或者:

SELECT   Foo.*
FROM     Foo JOIN (
           SELECT   foo_id id, MIN(ts) min_ts
           FROM     Bar
           GROUP BY id
         ) t USING (id)
WHERE    t.min_ts >= '2014-01-20'

或者:

SELECT   Foo.*
FROM     Foo JOIN Bar ON Bar.foo_id = Foo.id
GROUP BY Foo.id
HAVING   SUM(Bar.ts >= '2014-01-20')
 AND NOT SUM(Bar.ts <  '2014-01-20')

或者:

SELECT   *
FROM     Foo
WHERE    EXISTS (
           SELECT *
           FROM   Bar
           WHERE  Bar.foo_id = Foo.id
              AND Bar.ts >= '2014-01-20'
         )
 AND NOT EXISTS (
           SELECT *
           FROM   Bar
           WHERE  Bar.foo_id = Foo.id
              AND Bar.ts <  '2014-01-20'
         )

或者:

SELECT   b1.foo_id
FROM     Bar b1 LEFT JOIN Bar b2
      ON b2.foo_id = b1.foo_id
     AND b2.ts <  '2014-01-20'
WHERE    b1.ts >= '2014-01-20'
     AND b2.foo_id IS NULL
GROUP BY b1.foo_id

Consider two tables:

Foo:
  id INT,
  name VARCHAR

Bar:
  id INT,
  foo_id INT REFERENCES Foo(id),
  event SET('hit','miss'),
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Each Foo item can have multiple Bar events. How to query for Foo items which have Bar events after a specific datetime, but which also do not have Bar events before that datetime?

For instance, consider:

Foo id=1:
    hit 2014-01-21
    hit 2014-01-22
    hit 2014-01-23

Foo id=2:
    hit 2014-01-17
    hit 2014-01-19
    hit 2014-01-21

Foo id=3:
    hit 2014-01-14
    hit 2014-01-15
    hit 2014-01-18

Foo id=4:
    hit 2014-01-21
    hit 2014-01-22
    hit 2014-01-24

I would like to query only those Foo items with hits on or after 2014-01-20, so that should return the ids 1 and 4.

I have tried joining the Bar table to itself, using a few variations on the following:

SELECT
    b1.id
FROM
    Bar b1 JOIN Bar b2 using (foo_id)
WHERE
    b1.ts > '2014-01-20';

However, I don't see how to phrase the WHERE clause to exclude any combinations for which any date is before 2014-01-20.

解决方案

SELECT   Foo.*
FROM     Foo JOIN Bar ON Bar.foo_id = Foo.id
GROUP BY Foo.id
HAVING   MIN(Bar.ts) >= '2014-01-20'

Or:

SELECT   Foo.*
FROM     Foo JOIN (
           SELECT   foo_id id, MIN(ts) min_ts
           FROM     Bar
           GROUP BY id
         ) t USING (id)
WHERE    t.min_ts >= '2014-01-20'

Or:

SELECT   Foo.*
FROM     Foo JOIN Bar ON Bar.foo_id = Foo.id
GROUP BY Foo.id
HAVING   SUM(Bar.ts >= '2014-01-20')
 AND NOT SUM(Bar.ts <  '2014-01-20')

Or:

SELECT   *
FROM     Foo
WHERE    EXISTS (
           SELECT *
           FROM   Bar
           WHERE  Bar.foo_id = Foo.id
              AND Bar.ts >= '2014-01-20'
         )
 AND NOT EXISTS (
           SELECT *
           FROM   Bar
           WHERE  Bar.foo_id = Foo.id
              AND Bar.ts <  '2014-01-20'
         )

Or:

SELECT   b1.foo_id
FROM     Bar b1 LEFT JOIN Bar b2
      ON b2.foo_id = b1.foo_id
     AND b2.ts <  '2014-01-20'
WHERE    b1.ts >= '2014-01-20'
     AND b2.foo_id IS NULL
GROUP BY b1.foo_id

这篇关于仅在特定日期之后选择带有酒吧事件的foo的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！