SQL使用的一些技巧

原创已于 2023-06-13 16:31:47 修改 · 205 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

于 2023-06-13 14:12:43 首次发布

数据库专栏收录该内容

13 篇文章

订阅专栏

该SQL查询用于统计不同库存状态的数量，包括无库存、已记录、库存过剩、库存损失和库存平衡。同时，它使用INNERJOIN连接两个表并根据删除标志进行过滤。如果提供的关键字不为空，则会进行模糊匹配，检查初始地址、库存地址或创建者是否包含指定的文本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql中统计多个维度的数据

select
sum(case when 1=1 then 1  end)                          total,
sum(IF(i.inventory_status = 0, 1, 0))                   noneInventory,
sum(IF(i.inventory_status = 1, 1, 0))                   inventoried,
sum(IF(i.inventory_status = 2, 1, 0))                   inventorySurplus,
sum(IF(i.inventory_status = 3, 1, 0))                   inventoryLoss,
sum(IF(i.inventory_status = 4, 1, 0)) inventoryBalance
 from inventory i
           inner join asset a
           on a.id = i.property_id
           where i.delete_flag = 0
           and a.delete_flag = 0
           and i.inventory_task_id = '1668438195769454594'
           and (i.initial_addr like concat ('%','仓库','%')
           or i.inventory_addr like concat ('%','仓库','%')
           or i.create_by like concat ('%','仓库','%'))

或者使用 case when

select  
sum(case when flag=0 then 1 else 0 end) cnt1,  
sum(case when flag=1 then 1 else 0 end) cnt2,  
sum(case when flag=2 then 1 else 0 end) cnt3,  
from table;

sql中用一个文本来模糊匹配多个列

<select id="selectStatisticsByCondition" resultType="com.fawkes.common.inventory.check.common.model.InventoryStatisticsModel" parameterType="Map">
    select
      sum(case when 1=1 then 1  end)                          total,
      sum(IF(i.inventory_status = 0, 1, 0))                   noneInventory,
      sum(IF(i.inventory_status = 1, 1, 0))                   inventoried,
      sum(IF(i.inventory_status = 2, 1, 0))                   inventorySurplus,
      sum(IF(i.inventory_status = 3, 1, 0))                   inventoryLoss,
      sum(IF(i.inventory_status = 4, 1, 0))                   inventoryBalance
    from inventory i
           inner join asset a
           on a.id = i.property_id
    where i.delete_flag = 0
      and a.delete_flag = 0
      and i.inventory_task_id = #{inventoryTaskId,jdbcType=BIGINT}
    <if test="keyWords != null and keyWords != ''" >
      and (i.initial_addr like concat ('%',#{keyWords},'%')
      or i.inventory_addr like concat ('%',#{keyWords},'%')
      or i.create_by like concat ('%',#{keyWords},'%'))
    </if>
  </select>