mybatis中 count()按条件查询

悠悠 2022-11-20 02:59 450阅读 0赞

**1、sql count()函数:**

count()函数返回匹配指定条件的行数。

**sql count(column\_name)语法：**

count(column\_name)函数返回指定列的值的数目（null）不计入。

select count(column_name) from table_name

**sql count(\*)语法：**

count(\*)函数返回表中的记录数。

select count(*) from table_name

**sql count(distinct column\_name)语法：**

count(distinct column\_name)函数返回指定列的不同值的数目。

select count(distinct column_name) from table_name

比如下面这张表：table\_aid

+-----+---------+-------+------------+
    | aid | site_id | count | date       |
    +-----+---------+-------+------------+
    |   1 |       1 |    45 | 2016-05-10 |
    |   2 |       3 |   100 | 2016-05-13 |
    |   3 |       1 |   230 | 2016-05-14 |
    |   4 |       2 |    10 | 2016-05-14 |
    |   5 |       5 |   205 | 2016-05-14 |
    |   6 |       4 |    13 | 2016-05-15 |
    |   7 |       3 |   220 | 2016-05-15 |
    |   8 |       5 |   545 | 2016-05-16 |
    |   9 |       3 |   201 | 2016-05-17 |
    +-----+---------+-------+------------+

**执行sql语句：**

//特定条件下指定列的数目
    select count(count) as nums from table_aid
    where site_id = 3
    //输出结果：nums值为：521
    
    //计算table_aid中总记录数
    select count(*) as nums from table_aid
    //输出结果：nums值为：9
    
    //指定列的不同值的数目
    select count(distinct site_id) as nums from table_aid
    //输出结果：nums值为：5

**2、mybatis中count()按条件查询**

任务描述：数据库其中有两个字段分别为 1、站点：station、2、状态：status，status的取值为success或者fail。现在需求为将记录按站点分组，且要统计出其中的status为success的数量和为fail的数量。

**mybatis代码：**

<resultMap id="GroupBaseResultMap" type="java.util.Map">
        <result column="station" jdbcType="VARCHAR" property="station" />
        <result column="successNum" jdbcType="VARCHAR" property="successNum" />
        <result column="totalNum" jdbcType="VARCHAR" property="totalNum"/>
      </resultMap>
      
      <select id="selectGroupByStation" resultMap="GroupBaseResultMap">
        select rfr.station, count(rfr.status='success' or null) as successNum, count(rfr.status='fail' or null) as failNum, count(1) as totalNum
        from rec_file_report rfr
        GROUP BY station
      </select>

**测试结果为：**

{
            "failNum": 2,
            "totalNum": 73,
            "successNum": 71,
            "station": "admin"
        },
        {
            "failNum": 26,
            "totalNum": 521,
            "successNum": 495,
            "station": "changjiu.shao@wisdom56.com"
        }