MySQL COUNT(*) GROUP是否使用COUNT=？
MySQL COUNT(*) GROUP是否使用COUNT=？
提问于 2012-07-01 12:07:50
这是我的问题
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输出结果
count Toys date
3 Bibi 2012-06-26
4 Baba 2012-06-26如何让MYSQL只显示count=4的结果？
回答 2
Stack Overflow用户
发布于 2012-07-01 12:17:02
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;请注意，为了正确地进行数据分组，必须在GROUP BY子句中包含所有列，或者将所有列作为聚合函数的参数。MySQL特性允许您避免此限制，但它可能会导致意外的结果。
Stack Overflow用户
发布于 2012-07-01 12:22:41
首先需要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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11279495
复制相关文章
相似问题
腾讯云开发者
