How to Count Conditional Records with MyBatis and MySQL

Scenario

The business table stores records for all employees, and the goal is to count how many records satisfy certain conditions up to the current moment, for example the number of employees meeting the criteria in the current month.

Implementation for the Current Month

A MyBatis <select> statement can be defined to count distinct employee numbers (gh) for the current month:

<select id="selectCurrentMonthNum" resultType="Integer">
    SELECT count(*) FROM (
        SELECT gh FROM dp_ddjl
        WHERE sh = 1 AND sfcl = 1 AND htsfcl = 1
          AND date_format(ddsj,'%y%m') = date_format(#{currnetMonth},'%y%m')
        GROUP BY gh
    ) a
</select>

Note that the outer query must be given an alias (here a). The column gh represents the employee number used for classification. The parameter currnetMonth supplies the current date, from which the month part is extracted.

Implementation for Counting Up to Today

Another <select> statement counts records whose end date is earlier than today:

<select id="selectHtDqNum" resultType="Integer">
    SELECT count(*) FROM (
        SELECT gh FROM ht_htcx
        WHERE isdelete = 0
          AND date_format(#{today},'%y%m%d') > date_format(jssj,'%y%m%d')
        GROUP BY gh
    ) a
</select>

This query filters out deleted rows ( isdelete = 0) and compares the formatted dates to ensure only records ending before the supplied today value are counted.