对另一个查询的结果执行查询？ [英] Performing a query on a result from another query?

问题描述

我有一个查询：

SELECT availables.bookdate AS Date, DATEDIFF(now(),availables.updated_at) as Age
FROM availables
INNER JOIN rooms
ON availables.room_id=rooms.id
WHERE availables.bookdate BETWEEN '2009-06-25' AND date_add('2009-06-25', INTERVAL 4 DAY) AND rooms.hostel_id = 5094
GROUP BY availables.bookdate

其中返回如下：

Date               Age
2009-06-25         0
2009-06-26         2
2009-06-27         1
2009-06-28         0
2009-06-29         0

然后如何对返回的行数（在这种情况下为5）和年龄的SUM做一个计数？要只返回一行Count和SUM？

How can I then do a Count on the number of rows which is returned.. (in this case 5) and an SUM of the Ages? To return just one row with the Count and the SUM?

Count         SUM
5             3

感谢

推荐答案

你可以插入查询的结果（基本上是一个表）作为另一个查询的FROM子句源
，所以这样的东西将被写成：

Usually you can plug a Query's result (which is basically a table) as the FROM clause source of another query, so something like this will be written:

SELECT COUNT(*), SUM(SUBQUERY.AGE) from
(
  SELECT availables.bookdate AS Date, DATEDIFF(now(),availables.updated_at) as Age
  FROM availables
  INNER JOIN rooms
  ON availables.room_id=rooms.id
  WHERE availables.bookdate BETWEEN '2009-06-25' AND date_add('2009-06-25', INTERVAL 4 DAY) AND rooms.hostel_id = 5094
  GROUP BY availables.bookdate
) AS SUBQUERY

这篇关于对另一个查询的结果执行查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！