MySQL：包含 COUNT 个 SELECT 查询结果作为列（不分组）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3826572/
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
MySQL: Include COUNT of SELECT Query Results as a Column (Without Grouping)
提问by Hammer Bro.
I have a simple report sending framework that basically does the following things: It performs a SELECT query, it makes some text-formatted tables based on the results, it sends an e-mail, and it performs an UPDATE query.
我有一个简单的报告发送框架，它基本上执行以下操作：执行 SELECT 查询，根据结果制作一些文本格式的表，发送电子邮件，并执行 UPDATE 查询。
This system is a generalization of an older one, in which all of the operations were hard coded. However, in pushing all of the logic of what I'd like to do into the SELECT query, I've run across a problem.
该系统是旧系统的推广，其中所有操作都是硬编码的。但是，在将我想做的所有逻辑推入 SELECT 查询时，我遇到了一个问题。
Before, I could get most of the information for my text tables by saying:
以前，我可以通过以下语句获取文本表的大部分信息：
SELECT Name, Address FROM Databas.Tabl WHERE Status='URGENT';
Then, when I needed an extra number for the e-mail, also do:
然后，当我需要一个额外的电子邮件号码时，也可以这样做：
SELECT COUNT(*) FROM Databas.Tabl WHERE Status='URGENT' AND TimeLogged='Noon';
Now, I no longer have the luxury of multiple SELECT queries. What I'd like to do is something like:
现在，我不再有多个 SELECT 查询的奢侈。我想做的是：
SELECT Tabl.Name, Tabl.Address, COUNT(Results.UID) AS Totals
FROM Databas.Tabl
LEFT JOIN Databas.Tabl Results
ON Tabl.UID = Results.UID
AND Results.TimeLogged='Noon'
WHERE Status='URGENT';
This, at least in my head, says to get a total count of all the rows that were SELECTed and also have some conditional.
这至少在我的脑海中是说要获得所有被选择的行的总数，并且还有一些条件。
In reality, though, this gives me the "1140 - Mixing of GROUP columns with no GROUP columns illegal if no GROUP BY" error. The problem is, I don't want to GROUP BY. I want this COUNT to redundantly repeat the number of results that SELECT found whose TimeLogged='Noon'. Or I want to remove the AND clause and include, as a column in the result of the SELECT statement, the number of results that that SELECT statement found.
但是，实际上，这给了我“1140 - 如果没有 GROUP BY，则将没有 GROUP 列的 GROUP 列混合是非法的”错误。问题是，我不想 GROUP BY。我希望这个 COUNT 重复重复 SELECT 找到的 TimeLogged='Noon' 的结果数。或者我想删除 AND 子句，并将该 SELECT 语句找到的结果数作为列包含在 SELECT 语句的结果中。
GROUP BY is not the answer, because that causes it to get the COUNT of only the rows who have the same value in some column. And COUNT might not even be the way to go about this, although it's what comes to mind. FOUND_ROWS() won't do the trick, since it needs to be part of a secondary query, and I only get one (plus there's no LIMIT involved), and ROW_COUNT() doesn't seem to work since it's a SELECT statement.
GROUP BY 不是答案，因为这会导致它仅获取某些列中具有相同值的行的 COUNT。而 COUNT 甚至可能不是解决这个问题的方法，尽管它是我想到的。FOUND_ROWS() 不会解决问题，因为它需要成为辅助查询的一部分，而我只得到一个（而且不涉及 LIMIT），而 ROW_COUNT() 似乎不起作用，因为它是一个 SELECT 语句。
I may be approaching it from the wrong angle entirely. But what I want to do is get COUNT-type information about the results of a SELECT query, as well as all the other information that the SELECT query returned, in one single query.
我可能完全从错误的角度接近它。但我想要做的是在单个查询中获取有关 SELECT 查询结果的 COUNT 类型信息，以及 SELECT 查询返回的所有其他信息。
=== Here's what I've got so far ===
===这是我到目前为止所得到的===
SELECT Tabl.Name, Tabl.Address, Results.Totals
FROM Databas.Tabl
LEFT JOIN (SELECT COUNT(*) AS Totals, 0 AS Bonus
FROM Databas.Tabl
WHERE TimeLogged='Noon'
GROUP BY NULL) Results
ON 0 = Results.Bonus
WHERE Status='URGENT';
This does use sub-SELECTs, which I was initially hoping to avoid, but now realize that hope may have been foolish. Plus it seems like the COUNTing SELECT sub-queries will be less costly than the main query since the COUNT conditionals are all on one table, but the real SELECT I'm working with has to join on multiple different tables for derived information.
这确实使用了子 SELECT，我最初希望避免这种情况，但现在意识到希望可能是愚蠢的。另外，由于 COUNT 条件都在一个表上，因此 COUNTing SELECT 子查询似乎比主查询成本更低，但我正在使用的真正 SELECT 必须连接多个不同的表以获取派生信息。
The key realizations are that I can GROUP BY NULL, which will return a single result so that COUNT(*) will actually catch everything, and that I can force a correlation to this column by just faking a Bonus column with 0 on both tables.
关键的实现是我可以 GROUP BY NULL，这将返回一个结果，以便 COUNT(*) 实际上可以捕获所有内容，并且我可以通过在两个表上伪造一个带有 0 的 Bonus 列来强制与该列相关联。
It looks like this is the solution I will be using, but I can't actually accept it as an answer until tomorrow. Thanks for all the help.
看起来这是我将使用的解决方案，但直到明天我才能真正接受它作为答案。感谢所有的帮助。
回答by Hammer Bro.
SELECT Tabl.Name, Tabl.Address, Results.Totals
FROM Databas.Tabl
LEFT JOIN (SELECT COUNT(*) AS Totals, 0 AS Bonus
FROM Databas.Tabl
WHERE TimeLogged='Noon'
GROUP BY NULL) Results
ON 0 = Results.Bonus
WHERE Status='URGENT';
I figured this out thanks to ideas generated by multiple answers, although it's not actually the direct result of any one. Why this does what I need has been explained in the edit of the original post, but I wanted to be able to resolve the question with the proper answer in case anyone else wants to perform this silly kind of operation. Thanks to all who helped.
多亏了多个答案产生的想法，我才弄明白了这一点，尽管它实际上并不是任何人的直接结果。为什么这样做我需要的已经在原始帖子的编辑中解释过，但我希望能够用正确的答案解决这个问题，以防其他人想要执行这种愚蠢的操作。感谢所有帮助过的人。
回答by Phil Gilmore
You could probably do a union instead. You'd have to add a column to the original query and select 0 in it, then UNION that with your second query, which returns a single column. To do that, the second query must also select empty fields to match the first.
你可能可以做一个工会。您必须在原始查询中添加一列并在其中选择 0，然后将其与第二个查询联合，这将返回单个列。为此，第二个查询还必须选择空字段以匹配第一个。
SELECT Cnt = 0, Name, Address FROM Databas.Tabl WHERE Status='URGENT'
UNION ALL
SELECT COUNT(*) as Cnt, Name='', Address='' FROM Databas.Tabl WHERE Status='URGENT' AND TimeLogged='Noon';
It's a bit of a hack, but what you're trying to do isn't ideal...
这有点黑客，但你想要做的并不理想......
回答by Martin Smith
Does this do what you need?
这能满足您的需求吗？
SELECT Tabl.Name ,
Tabl.Address ,
COUNT(Results.UID) AS GrandTotal,
COUNT(CASE WHEN Results.TimeLogged='Noon' THEN 1 END) AS NoonTotal
FROM Databas.Tabl
LEFT JOIN Databas.Tabl Results
ON Tabl.UID = Results.UID
WHERE Status ='URGENT'
GROUP BY Tabl.Name,
Tabl.Address
WITH ROLLUP;
回答by zigdon
The API you're using to access the database should be able to report to you how many rows were returned - say, if you're running perl, you could do something like this:
您用来访问数据库的 API 应该能够向您报告返回了多少行 - 例如，如果您正在运行 perl，您可以执行以下操作：
my $sth = $dbh->prepare("SELECT Name, Address FROM Databas.Tabl WHERE Status='URGENT'");
my $rv = $sth->execute();
my $rows = $sth->rows;
回答by Ascherer
Grouping by Tabl.id i dont believe would mess up the results. Give it a try and see if thats what you want.
按 Tabl.id 分组我不相信会弄乱结果。试一试，看看这是否是你想要的。
