SQL如何按coupon_id、when_added分组统计关联查询行数
要实现你的统计需求,核心是通过GROUP BY 对 coupon_id 和 when_added 做联合分组,再用聚合函数统计每组的行数即可,修改后的查询语句如下:
SELECT r.coupon_id, c.who_added, c.coupon_name, r.when_added, COUNT(*) AS total_count FROM redeemed_coupons r JOIN coupon c ON r.coupon_id = c.coupon_id AND c.who_added = 1 AND r.when_added BETWEEN 1602827745 AND 1613084678 GROUP BY r.coupon_id, c.who_added, c.coupon_name, r.when_added ORDER BY r.when_added
逻辑说明
- 分组规则为
coupon_id + when_added联合匹配,只有两个字段值完全一致的行才会被归为同一组计数,从规则上保证了不同coupon_id即使when_added取值相同,也会分开统计 - 同一coupon_id下相同的when_added会被合并计算行数,对应你示例里coupon_id=4的2条相同时间记录,会自动统计为
total_count=2 - 所有非聚合字段都被纳入分组列表,兼容MySQL等数据库开启
ONLY_FULL_GROUP_BY严格模式的语法要求,不会出现语法报错 - 输出字段和你期望的格式完全匹配
内容的提问来源于stack exchange,提问作者AgentRed
相关产品推荐
相关产品推荐

