如何查询返回按天分组的行数？
如何查询返回按天分组的行数？
提问于 2012-10-17 22:03:39
我使用的是这个表达式：
SELECT DATE(calldate) as Data,COUNT(clid) as Registros,dst as Tronco
FROM cdr WHERE dst=55008133070000
AND calldate BETWEEN '2012-10-01' AND '2012-10-15'
GROUP BY DATE(calldate), clid HAVING COUNT(clid) > 1 ORDER BY Data DESC
Date---------Total------ Trunk
2012-10-11 3 55008133070000
2012-10-11 2 55008133070000
2012-10-11 3 55008133070000
2012-10-10 2 55008133070000
2012-10-10 2 55008133070000
2012-10-10 2 55008133070000
2012-10-09 3 55008133070000
2012-10-09 2 55008133070000
2012-10-09 2 55008133070000
2012-10-09 3 55008133070000
2012-10-09 5 55008133070000
2012-10-09 5 55008133070000但是，需要显示当天重复的次数。
Date---------Total------ Trunk
2012-10-11 3 55008133070000
2012-10-10 3 55008133070000
2012-10-09 6 55008133070000有什么建议吗？
回答 3
Stack Overflow用户
回答已采纳
发布于 2012-10-17 22:07:04
编辑的 due OP评论：
您应该按dst分组，而不是按clid分组，对于不同的目标编号也要计数，请参见group by子句和计数：
SELECT DATE(calldate) as Data,COUNT( distinct clid) as Registros, dst as Tronco
FROM cdr
WHERE dst=55008133070000
AND calldate BETWEEN '2012-10-01' AND '2012-10-15'
GROUP BY DATE(calldate), dst
HAVING COUNT(distinct clid) > 1 ORDER BY Data DESCStack Overflow用户
发布于 2012-10-17 22:06:48
在您的查询中，您将使用GROUP BY DATE(calldate), clid，它将为您提供每天每个clid的COUNT()。
从GROUP BY子句中删除clid以获取每个日期的计数。
您的最终查询将类似于：
SELECT
DATE(calldate) as Data, COUNT(*) as Registros, dst as Tronco
FROM
cdr
WHERE
dst=55008133070000
AND calldate BETWEEN '2012-10-01' AND '2012-10-15'
GROUP BY
DATE(calldate)
HAVING
COUNT(clid) > 1
ORDER BY
Data DESC然而，在这个查询中，拉取dst可能没有意义，可以删除。
Stack Overflow用户
发布于 2012-10-17 22:08:59
使用Calldate代替Id。
SELECT DATE(calldate) as Data,COUNT(calldate) as Registros,dst as Tronco
FROM cdr WHERE dst=55008133070000
AND calldate BETWEEN '2012-10-01' AND '2012-10-15'
GROUP BY DATE(calldate), dst HAVING COUNT(calldate) > 1 ORDER BY Data DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12936129
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号