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

超、凢脫俗 2022-06-01 08:17 161阅读 0赞

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）字段进行统计，统计出各种类型原因的个数，在这个基础上关联其他几张表找出其他必须的信息

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

接着：使用上面提到的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') >= #{dto.startTime}
                </if>
                <if test="dto.endTime != '' and dto.endTime != null">
                    and to_char(tdh.create,'yyyy-mm-dd') <= #{dto.endTime}
                  </if>
            </where>
            group by tdh.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>