【sql查询】使用sql查询一个物品是否在有效期内的方法（数据库无这个字段通过生产日期和保质期进行计算得出）

最新推荐文章于 2024-06-29 21:57:14 发布

阿，葱来了-C is coming

最新推荐文章于 2024-06-29 21:57:14 发布

阅读量4.2k

点赞数 3

分类专栏：数据库文章标签：数据库 sql database

本文链接：https://blog.csdn.net/chirp_CQ/article/details/122824043

版权

SQL 查询优化过期状态聚合函数性能提升

关键词由CSDN通过智能技术生成

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

sql有效期查询和筛选的方式

事件起因

今天在写一个查询一个产品的使用期限的时候遇到这个问题，物品的使用期限问题稍微延伸一下的话就有很多方面的应用比如说食品的生产日期和保质期，危险物品的定期检测等系列问题所以特此来写篇文章来记录一下以便自己以后遇到相似的问题或者后来者遇到相似的问题

查看上面的目录不需要听我在这儿逼逼叨叨的，可以直接跳转到实际应用场景去查看对应的sql语句的写法书写事件经过只是给自己未来看的顺便描述下在什么样的场景下可能会用到这样的写法

问题描述：

当前的需求是一张mysql数据库表里面有一个产品的众多信息有两列分别是产品的生产日期和有效期现在要求能够通过前端控制查询超限的物品，并将他们按不同的颜色显示出来

sql给后端提供数据

因为这个问题是在对公司的项目进行业务增加的时候注意到的，所以不方便展示那个项目的数据库，但是我们可以来还原一下大致的业务场景，所以咱们先来建一个数据库表：
在这里插入图片描述
主要关注start_time,keep_time 分别可以表示为生成日期和有效期

然后向这个表插入两个数据：
在这里插入图片描述
根据使用start_time和keep_time （笔者的当前时间（执行这个sql的时间）是2022.02.09）来看第一条数据是过期了第二条数据没有过期，然后写下了让每一个物品的数据都通过聚合函数的方式来生成一条过期状态的表示（好让后端返回的每个物品的数据信息中带一个是否过期的字段方便前端对过期的数据以不同的方式（颜色或其他方式）进行展现）的sql：

SELECT *,DATE_ADD(start_time,INTERVAL keep_time month) < NOW() as overTimeStatus FROM `goods`

该条sql语句的执行效果是将原来的所有数据列后直接增加一列为 overTimeStatus 用于表示是否超限
sql的执行结果如下：
在这里插入图片描述
如图最后一列的overTimeStatus 就是用于表示是否超限（超出使用期限–过期） 1表示已经超限，0表示未超限（在使用期限内）

实际应用场景

在具体的业务中，我们要查询的数据列可能有很多列，就比如下面这个：
在这里插入图片描述
复制出来有这么多：

SELECT
        d.`name` AS dept_name,
        h.house_name AS houseName,
        h2.house_name as newspare24Name,
        d1.name as bankDeptName,
        d2.name as useDeptName,
        d3.name as oldDeptName,
        cs.`course_name` AS course_name,
        a.create_time,
        a.tb_config_id,
        a.update_time,
        c.tb_type,
        a.remark,
        a.overTimeStatus,
        DATE_FORMAT(a.spare2,'%Y-%m-%d') AS spare2
        
        FROM (select *,DATE_ADD(spare2,INTERVAL spare14 month) <![CDATA[<]]> now() as overTimeStatus from tb_assets)  AS a
        LEFT JOIN sys_dept AS d ON a.dept_id = d.dept_id
        LEFT JOIN sys_dept AS d1 ON a.spare9 = d1.dept_id
        LEFT JOIN sys_dept AS d2 ON a.spare10 = d2.dept_id
        LEFT JOIN sys_dept as d3 on a.spare23 = d3.dept_id
        LEFT JOIN sys_house AS h ON a.spare24 = h.house_id
        LEFT JOIN sys_house AS h2 ON a.newspare24 = h2.house_id
        LEFT JOIN tb_config AS c ON a.tb_config_id=c.tb_config_id
        LEFT JOIN sys_course as cs on a.course_id = cs.course_id
        where a.overTimeStatus = 1
        ORDER BY
        a.create_time DESC,
        a.assets_id DESC

当然我将where那些赛选条件都给去除了，实际要更长一点，
注意上面的增加一列的方式是放在 from 中的
聚合函数计算得出的overTimeStatus 的值 1表示：已经过期了 0表示：还在有效期限内
sql语句的执行顺序如下图所示：
sql的执行顺序
所以对于最外层的查询语句的select来说，优先要执行 from 里面的内容而from里面又是一个子查询它会生成一个新的集合，所以优先执行了子查询里面的内容子查询里面的内容就是将通过聚合函数的方式来给原本的数据表增加一列新的值，也就是超限状态

优点：在进行其他所有的数据筛选条件前就直接将所有的列的是否超限状态给计算出来，能够把这列的超限状态作为后面的筛选条件比如说where overTimeStatus = 1 要知道overTimeStatus 这一列在数据本身是不存在的而是通过聚合函数计算出来的值
缺点：有优点就必定会存在它的缺点，当面临一个表中存在大量的数据时，比较花费时间因为不管那一列是否是满足自己其他的筛选条件都将优先计算完所有的行生成一个临时表，然后对这个临时表进行操作这样在进行一切操作前就处理所有的数据列去执行一遍聚合函数的方式有一些慢

针对上述sql缺点进行的一点改进

首先要清楚：聚合函数的结果要作为一列的返回值故将它添加到select语句中去

select (这儿一堆我就不写了),DATE_ADD(spare2,INTERVAL spare14 month) <![CDATA[<]]> now() as overTimeStatus from tb_assets
where (其他筛选条件) AND DATE_ADD(spare2,INTERVAL spare14 month) <![CDATA[<]]> now()= 1

该方式将用于计算是否过期的状态的字段分别在select和where中进行计算了两次，根据sql执行顺序 where中先执行，但是执行后的结果不能够保存所以要得到结果值时再要将筛选条件后的结果执行一遍聚合函数得到这个列其他筛选条件放在了这个是否过期的状态的判断字段前面先通过其他的条件对范围进行了进一步缩小后再来对这个结果范围进行计算计算的次数将会大幅度减少当面临大量的数据时，这不失为一种办法