邮政总局的“开幕秀”

Question

我有一张表演桌和一张表演桌。

我正在寻找“即将开演的节目”，要求“其第一场演出在下周内举行”。

以下是相关的数据字段：

秀场

id

名称(varchar)

..。

表演

id

show_id (int/fk)

date_time (日期时间)

..。

我认为这里需要“按条款分组”，但我还没有弄清楚。

任何帮助都会很棒。

谢谢!

Answer 1

试试这个：

SELECT shows.name, performances.date_time AS "Opening Date"
FROM shows
INNER JOIN PERFORMANCES
ON shows.id = performances.show_id
WHERE performances.date_time <= (CURRENT_DATE + interval '1 week')
AND  performances.date_time > CURRENT_DATE

样本SQL Fiddle输出

PostgreSQL 9.3.1架构设置

CREATE TABLE Shows
    ("id" int, "name" varchar(20))
;

INSERT INTO Shows
    ("id", "name")
VALUES
    (1, 'Chicago'),
    (2, 'Cats'),
    (3, 'Some other show')
;

CREATE TABLE Performances
    ("id" int, "show_id" int, "date_time" date)
;

INSERT INTO Performances
    ("id", "show_id", "date_time")
VALUES
    (1, 1, '2014-04-18'),
    (2, 2, '2014-05-18'),
    (3, 3, '2012-05-18');
;

查询1

SELECT shows.name, performances.date_time AS "Opening Date"
FROM shows
INNER JOIN PERFORMANCES
ON shows.id = performances.show_id
WHERE performances.date_time <= (CURRENT_DATE + interval '1 week')
AND  performances.date_time > CURRENT_DATE

结果

|    NAME |                 OPENING DATE |
|---------|------------------------------|
| Chicago | April, 18 2014 00:00:00+0000 |