SQL 对另一个查询的结果执行查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/949465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performing a query on a result from another query?
提问by holden
I have a the 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
Which returns something like:
返回如下内容：
Date Age
2009-06-25 0
2009-06-26 2
2009-06-27 1
2009-06-28 0
2009-06-29 0
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?
然后我如何对返回的行数进行计数..（在这种情况下为 5）和年龄的总和？只返回带有 Count 和 SUM 的一行？
Count SUM
5 3
Thanks
谢谢
回答by SWeko
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:
通常你可以插入一个 Query 的结果（基本上是一个表）作为另一个查询的 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
回答by soulmerge
You just wrap your query in another one:
您只需将查询包装在另一个查询中：
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;
回答by Talljoe
I don't know if you even need to wrap it. Won't this work?
我不知道你是否需要包装它。这行不通？
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);
If your goal is to return both result sets then you'll need to store it some place temporarily.
如果您的目标是返回两个结果集，那么您需要将其临时存储在某个地方。
回答by Gio2k
Note that your initial query is probably not returning what you want:
请注意，您的初始查询可能不会返回您想要的内容：
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
You are grouping by book date, but you are not using any grouping function on the second column of your query.
您正在按图书日期分组，但您没有在查询的第二列上使用任何分组功能。
The query you are probably looking for is:
您可能正在寻找的查询是：
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
