MySQL COUNT(*) GROUP BY HAVING COUNT=?
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11279495/
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 COUNT(*) GROUP BY HAVING COUNT=?
提问by user1478893
This my Query
这是我的查询
SELECT COUNT(*) as total, toys, date FROM T1
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
UNION
SELECT COUNT(*) as total, toys, date FROM T2
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
UNION
SELECT COUNT(*) as total, toys, date FROM T3
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType
HAVING COUNT( total ) = 4
Output result
输出结果
count Toys date
3 Bibi 2012-06-26
4 Baba 2012-06-26
How can i get MYSQL to show results only for count=4
我怎样才能让 MYSQL 只显示 count=4 的结果
回答by vyegorov
SELECT * FROM (
SELECT COUNT(*) as total, toys, date FROM T1
WHERE (date >= '2012-06-26' AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType
UNION
SELECT COUNT(*) as total, toys, date FROM T2
WHERE (date >= '2012-06-26' AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType
UNION
SELECT COUNT(*) as total, toys, date FROM T3
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType) AS src
WHERE total = 4;
Please, note, that for proper data groupping you musthave all columns either in the GROUP BYclause or as arguments to the aggregate functions. It is MySQL feature to allow you to avoid this restriction, but it might lead you to the unexpected results.
请注意，对于正确的数据分组，您必须将所有列都包含在GROUP BY子句中或作为聚合函数的参数。MySQL 的特性可以让您避免这种限制，但它可能会导致您意想不到的结果。
回答by xdazz
You need group by first then union the result.
您首先需要 group by 然后联合结果。
SELECT COUNT(*) as total, toys, date FROM T1
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType HAVING COUNT( *) = 4
UNION
SELECT COUNT(*) as total, toys, date FROM T2
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType HAVING COUNT( * ) = 4
UNION
SELECT COUNT(*) as total, toys, date FROM T3
WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType HAVING COUNT( * ) = 4
