数据分组、统计 case when then else end

case when 对表进行条件分组

case简单函数 case   age  when   then

    select name , sex , age , (

    case age 

     /*when 条件成立显示then中内容 then 成立是显示  else 不成立显示 end*/

    when age = 18 then '成年人' else '未成年' end

    when age = 30 then '而立之年' else '小伙子' end

    ) 身份   /*列名*/

    from user

name      sex     age         身份

张三         男       19         成年人

李四        男       30        而立之年

 

case 搜索函数 case when then

     SELECT COUNT((
            CASE
            WHEN condition = ''   THEN condition
            ELSE NULL
            END

             )) col1 ,             //根据condition 分组，并统计condition = ''出现的次数
            COUNT((
            CASE
            WHEN condition = ''   THEN condition
            ELSE NULL
            END

             )) col2 ,
            count(id) count
            FROM  table

有如下需求:
  表A中有很多条数据, 每条数据拥有一个状态字段,现在需要统计不同状态的数据有多少条.

查询出来的结果如下形式:

status1  status2   status3  count
   2             3            3          8

   SELECT COUNT((
        CASE
        WHEN STATUS = 'status1'
        THEN STATUS
        ELSE NULL
        END))status1,

        COUNT((
        CASE
        WHEN STATUS = 'status2'
        THEN STATUS
        ELSE NULL
        END))status2,

        COUNT((
        CASE
        WHEN STATUS = 'status3'
        THEN STATUS
        ELSE NULL
        END))status3,
count(table.id) count
        FROM table 

 需求是：根据t_dev表找出其中所有记录在t_history和t_history_details两张表中存储的告警原因统计，

即根据不同原因（tdhd.cause）字段进行统计，统计出各种类型原因的个数，在这个基础上关联其他几张表找出其他必须的信息

首先根据总表找出关联表内必须的数据：

 

select tsd.id device_id,NVL(tui.unit_name,tbi.build_name) place_name,
            alarmCount.TEMPERATURE,alarmCount.EXCESSIVE,alarmCount.VOLTAGE,alarmCount.total
        from  t_dev tsd
        join t_fire tef on tef.id = tsd.id and tsd.status = 'VALID'
        join t_unit tui on tui.id = tsd.unit_id
        join t_build tbi on tbi.id = tsd.place_id
        <where>
           查找条件；
        </where>

 

接着：使用上面提到的case when 方法进行数据统计

 

SELECT COUNT((
                        CASE
                        WHEN tdhd.alarm_cause = 'TEMPERATURE'   THEN tdhd.cause
                        ELSE NULL
                        END
                        )) TEMPERATURE ,
                    COUNT((
                        CASE
                        WHEN tdhd.alarm_cause = 'EXCESSIVE'   THEN tdhd.cause
                        ELSE NULL
                        END
                        )) EXCESSIVE ,
                    COUNT((
                        CASE
                        WHEN tdhd.alarm_cause = 'VOLTAGE'   THEN tdhd.cause
                        ELSE NULL
                        END
                        )) VOLTAGE ,
                    count(tdh.dev_id) total
        from t_history tdh
        join t_history_details tdhd on tdhd.dev_history_id = tdh.id 
        where 统计条件

 

然后将统计出来的数据当成一张临时表，关联进第一步的sql中， 在这里有个问题， 关联表需要关联条件，这里的条件是统计出来的数据的有个dev_id字段相同,

所以需要在统计sql中将dev_id查询出来, 自然就想到了group by,使用dev_id分组,这样每组统计都有对应的dev_id,然后就可以将两个sql关联起来了

以下是最终结果

 

select tsd.id device_id,NVL(tui.unit_name,tbi.build_name) place_name,
            alarmCount.TEMPERATURE,alarmCount.EXCESSIVE,alarmCount.VOLTAGE,alarmCount.total
        from  t_dev tsd
        join t_fire tef on tef.id = tsd.id and tsd.status = 'VALID'
        join t_unit tui on tui.id = tsd.unit_id
        join t_build tbi on tbi.id = tsd.place_id
        join (SELECT COUNT((
                        CASE
                        WHEN tdhd.alarm_cause = 'TEMPERATURE'   THEN tdhd.cause
                        ELSE NULL
                        END
                        )) TEMPERATURE ,
                    COUNT((
                        CASE
                        WHEN tdhd.alarm_cause = 'EXCESSIVE'   THEN tdhd.cause
                        ELSE NULL
                        END
                        )) EXCESSIVE ,
                    COUNT((
                        CASE
                        WHEN tdhd.alarm_cause = 'VOLTAGE'   THEN tdhd.cause
                        ELSE NULL
                        END
                        )) VOLTAGE ,
                    count(tdh.dev_id) total,
                    tdh.dev_id
        from t_history tdh
        join t_history_details tdhd on tdhd.dev_history_id = tdh.id
        <where>
            <if test="dto.startTime != '' and dto.startTime != null">
                and to_char(tdh.create,'yyyy-mm-dd') &gt;= #{dto.startTime}
            </if>
            <if test="dto.endTime != '' and dto.endTime != null">
                and to_char(tdh.create,'yyyy-mm-dd') &lt;= #{dto.endTime}
              </if>
        </where>
        group by tdh.dev_id                                  <!--分组,获取dev_id-->
        ) alarmCount  on alarmCount.dev_id = tef.id
        <where>
            <if test="dto.placeName != '' and dto.placeName != null">
               and (tbi.build LIKE '%'||#{dto.placeName}||'%' or tui.unit like '%'||#{dto.placeName}||'%')
            </if>
        </where>