从另一个查询结果中执行查询？

Question

我有以下查询：

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
5             3

谢谢

- holden

Answer 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

- SWeko

---

问题是，子查询会为外部查询的每个记录运行吗？还是结果会被缓存？ - steff_bdh

SQL Server最有可能做的是只运行子查询一次，然后对结果进行流聚合。您可以使用“包括实际执行计划”选项来查看它实际上所做的。 - SWeko

Answer 2

你只需要在查询语句外套一层：

SELECT COUNT(*), SUM(Age)
FROM (
    SELECT availables.bookdate AS Count, 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 tmp;

- soulmerge

Answer 3

我不确定你是否需要包装它。这样行吗？

SELECT COUNT(*), SUM(DATEDIFF(now(),availables.updated_at))
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);

如果你的目标是返回两个结果集，那么你需要将它们暂时存储在某个地方。

- Talljoe

Answer 4

请注意，您的初始查询可能没有返回您想要的结果：

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

你正在按照图书日期进行分组，但在查询的第二列上没有使用任何分组函数。

你可能正在寻找的查询语句是：

SELECT availables.bookdate AS Date, count(*) as subtotal, sum(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

- Gio2k