对另一个查询的结果执行查询？

一尘不染

对另一个查询的结果执行查询？

sql

我有一个查询：

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）和年龄的总和？要仅返回带有Count和SUM的一行？

Count         SUM
5             3

谢谢

阅读 154

2021-03-17

共1个答案

一尘不染

通常，您可以将查询的结果（基本上是一个表）插入为另一个查询的FROM子句源，因此将这样编写：

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

2021-03-17