Mysql 根据时间统计总数

最新推荐文章于 2023-06-02 18:07:43 发布

转载最新推荐文章于 2023-06-02 18:07:43 发布 · 342 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/sunxun/p/9796116.html

文章标签：

#数据库

本文分享了一段SQL代码，用于统计学校考试中按日期分组的缴费次数、缴费人数及金额，涉及内连接、条件筛选及聚合函数的使用。

代码写法：

    SELECT
            date_format(examinee_pay_time, '%Y-%m-%d') as payDate,
            COUNT(examinee_id) As realityApplyCount, <!-- 日缴费次数 -->
            sum((CASE WHEN examinee_exam_score IS NULL THEN exam_fee ELSE 0 END )) as realityApplyFee, <!-- 日缴费金额 -->
            exam_id,
            exam_name,
            examinee_pay_status,
            examinee_exam_status
        FROM
            schoolexam
        INNER JOIN examinee e ON exam_id = exam_item_id    AND  exam_type = examinee_apply_type
                                    AND (examinee_exam_status != '4' OR ISNULL(examinee_exam_status))
                                    AND examinee_pay_status = "1"
        <where>  
            1 = 1           
        </where>
        GROUP BY 
            date_format(examinee_pay_time, '%Y-%m-%d')