使用 case when then else end 进行数量统计

理解case when then else end 的使用

 

 Mysql搜索引擎是行搜索，一行一行往下，所以匹配也是每一行进行匹配，符合条件的返回

1、一列里面进行一次匹配

SELECT (CASE WHEN a.`name`='张三' THEN a.core ELSE 0 END) AS '张三' FROM student a ;

上面的执行过程：

进行匹配的是名字name，首先匹配第一行，name是等于张三，所以返回core也就是50
匹配第二行，name等于李四，不匹配，返回else的值也就是0
匹配第三行，name等于王五，不匹配，返回else的值也就是0
匹配第四行，name等于赵六，不匹配，返回else的值也就是0
所以结果集为

2、一列里面进行多次匹配

SELECT (CASE WHEN a.`name`='张三' THEN a.core WHEN a.`name`='李四' THEN a.core END) AS '张三' FROM student a ;

执行过程：第一行匹配张三成功，返回50，第二行匹配李四成功返回60，三四两行不匹配返回null

3. 数量统计

 select aa.materialcode,aa.componentcatalogname,aa.warehousename,aa.guige,aa.unit, --,sum(aa.stockcount) allcount,
  
  sum (case when aa.healthstate is not null then 1  else 0  end) SJT_STORECOUNT,
  sum (case when  aa.healthstate = 0  then 1 else 0 end ) SJT_GOODSTORECOUNT,
  sum (case when  aa.healthstate in ('1','2','3')  then 1 else 0 end ) SJT_REPAIRSTORECOUNT,  // 
  sum (case when   aa.healthstate in ('8','9') then 1 else 0 end ) SJT_BAOFEIRSTORECOUNT
 
  from 
( select 
       warehouse.WarehouseName warehousename,
       component.ComponentQrCode componentqrcode,
       component.ComponentCatalogName componentcatalogname,
       outinrecord.operatetime, component.builddate,  
       outinrecord.actioninfo,
       log.GuiGe guige,
       log.Unit unit,
       component.Price price,
       component.StockState stockstate,
       component.HEALTHSTATE healthstate,
       component.BatchOrSingle batchorsingle,
       component.jwdname,
       component.ComponentQrCode qrcode,
       com.AssertValue assetsvalue,
       
       log.lifetype,
       com.id,com.componentid,com.warehouseid,com.attrainid,com.atparentcomponentid,com.stockcount,com.assertvalue,com.componenttreepath,  
       component.materialcode materialcode
      
  from componentstock com
    LEFT JOIN warehouse on com.WarehouseId = warehouse.ID
    LEFT JOIN component on com.ComponentId = component.ID
    LEFT JOIN componentcatalog log on log.ComponentCatalogID = component.ComponentCatalogID
    left join componentoutinrecord outinrecord on component.id = outinrecord.componentid
    where  warehouse.WarehouseName is not null and  component.ComponentQrCode is not null
    ） aa  
     group by aa.materialcode,aa.componentcatalogname,aa.guige,aa.unit,aa.warehousename