如何将子查询包含在内连接中？

How can I include a subquery into an inner join?

如何将子查询包含在内连接中?

I have the following SQL:

我有以下SQL:

SELECT 
  date_trunc(
    'hour',
    FROM_UNIXTIME(timefrom)
  ) AS HourFrom,
  date_trunc(
    'hour',
    (FROM_UNIXTIME(timeto) + interval '45' minute)
  ) AS HourTo
FROM 
  reservation
ORDER BY
  date_trunc(
    'day',
    FROM_UNIXTIME(timefrom)
  )

That gives me data like this:

这给了我这样的数据:

TimeFrom                    TimeTo
2015-08-04 11:00:00.000     2015-08-04 14:00:00.000
2015-08-04 18:00:00.000     2015-08-04 20:00:00.000
2015-08-04 21:00:00.000     2015-08-04 23:00:00.000

I want to have a numbers table and do an inner join like the following:

我想要一个数字表,并进行如下所示的内连接:

SELECT DATE_ADD(HOUR, i - 1, TimeFrom) AS TimeFrom, 
       DATE_ADD(HOUR, i, TimeFrom) AS TimeTo
FROM (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
      UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
      UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16
      UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
      UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
) AS numbers
INNER JOIN mytable ON numbers.i <= DATE_DIFF(HOUR, TimeFrom, TimeTo)
ORDER BY TimeFrom

So I can get something like this:

所以我可以得到这样的东西:

TimeFrom                    TimeTo
2015-08-04 11:00:00.000     2015-08-04 12:00:00.000
2015-08-04 12:00:00.000     2015-08-04 13:00:00.000
2015-08-04 13:00:00.000     2015-08-04 14:00:00.000
2015-08-04 18:00:00.000     2015-08-04 19:00:00.000
2015-08-04 19:00:00.000     2015-08-04 20:00:00.000
2015-08-04 21:00:00.000     2015-08-04 22:00:00.000
2015-08-04 22:00:00.000     2015-08-04 23:00:00.000

How can I use the result of the first query to do the inner join(replace mytable with that query result)? Is this possible? I have not been able to find a way to plug in my query to perform an inner join on it.

如何使用第一个查询的结果进行内部连接(用该查询结果替换mytable)?这可能吗?我无法找到插入查询以在其上执行内部联接的方法。

I am not very experienced with SQL syntax and have not been able to find an answer for this despite looking all day.

我对SQL语法不是很有经验,尽管看了一整天,但仍无法找到答案。

1 个解决方案

#1

What you want to do is create a "derived table". That is, a table that derived from something else. Most times it's a view. But it can be done just the same with a typical SELECT statement. The syntax looks like this:

你想要做的是创建一个“派生表”。也就是说,一个源自其他东西的表。大多数时候它是一种观点。但它可以与典型的SELECT语句完全相同。语法如下所示:

SELECT dt.name 
from (SELECT name from tableWithName) dt

Where "dt" is the name of the table. When you reference it in the primary select, you must use the derived table name as a prefix. "dt.name"

其中“dt”是表的名称。在主选择中引用它时,必须使用派生表名作为前缀。 “dt.name”

The second query you have here actually HAS this set up correctly already. But the word AS is incorrect syntax. it should just say

你在这里的第二个查询实际上已经正确设置了。但AS这个词是不正确的语法。它应该说

...SELECT 24
) numbers

And all of your parts in the begining SELECT should use that table name as a prefix:

并且在开始SELECT中的所有部分都应该使用该表名作为前缀:

SELECT DATE_ADD(HOUR, i - 1, numbers.TimeFrom) AS TimeFrom

#1