mysql的not in函数存放的是一个子查询结果集，有limit会出现的问题

话不多说：代码容易踩的坑我来踩，免费的赞赞你来点。

有一个这样的需求：

有一张这样的数据表，比如叫sysProcess，字段名有

pro_id , pro_name , pro_type , created_datetime , creator , modified_datetime , modifier

比如现在有这几条数据

1        aa        1        2023-11-01 10:00:00  张三        null        null

2        aa        1        2023-11-02 10:00:00  张三        null        null

3        bb        2        2023-11-03 10:00:00  张三        null        null

4        bb        2        2023-11-04 10:00:00  张三        null        null

5        cc        3        2023-11-05 10:00:00  张三        null        null

6        cc        3        2023-11-06 10:00:00  张三        null        null

7        cc        3        2023-11-07 10:00:00  张三        null        null

8        cc        4        2023-11-08 10:00:00  张三        null        null

9        dd        5        2023-11-09 10:00:00  张三        null        null

10        dd       6        2023-11-09 11:00:00  张三        null        null

我现在要在主页面按照pro_type进行分组显示出创建时间是最新的（也就是created_datetime是最大的）数据，那么这时候我们的sql语句就是这样写：

select pro_id,pro_name,pro_type,max(created_datetime) as created_datetime , creator , modified_datetime , modifier from sys_process group by pro_type ;

这个sql语句可以这样写，但是也可以使用ROW_NUMBER(PARTITION BY C1 ORDER BY C2) 这个内置函数我在另一篇博客上面有写，可以去看一下。链接是：

mysql的ROW_NUMBER() OVER (PARTITION BY column 【分组】 ordery by column【排序】)详解-优快云博客

结果集如下：   

pro_id , pro_name , pro_type , created_datetime , creator , modified_datetime , modifier     

2        aa        1        2023-11-02 10:00:00  张三        null        null               

4        bb        2        2023-11-04 10:00:00  张三        null        null                

7        cc        3        2023-11-07 10:00:00  张三        null        null                

8        cc        4        2023-11-08 10:00:00  张三        null        null               

9        dd        5        2023-11-09 10:00:00  张三        null        null               

10       dd       6        2023-11-09 11:00:00  张三        null        null              

然后现在我们有个需求就是想查看某个pro_type的所有数据，但是这些数据里面不能包括创建时间是最新的这一条数据。

也就是比如上面标红的这一条数据pro_type是3，他的所有数据有：

pro_id , pro_name , pro_type , created_datetime , creator , modified_datetime , modifier

5        cc        3        2023-11-05 10:00:00  张三        null        null

6        cc        3        2023-11-06 10:00:00  张三        null        null

7        cc        3        2023-11-07 10:00:00  张三        null        null

但是此时查出的数据是不能包括id是7的，那么我们的思路就是先按照pro_type查出数据，然后再剔除掉创建时间是最新的。

①根据pro_type查询数据

select * from sys_process where pro_type = #{proType}  ；

②筛选创建时间是最新的

select id  from sys_process where pro_type = #{protype} order by created_datetime desc limit 1 ;

①和②结合

select * from sys_process where pro_type = #{proType}  

not in

(

select id  from sys_process where pro_type = #{protype} order by created_datetime desc limit 1

)

这样看起来没啥问题，将②的查询结果作为not in的条件。但是其实mysql是不允许这样写的。

我们可以将not in换成!= 或者 <>

但是如果我们执意要写成这种not in，那就必须再包一层：

①和②结合

select * from sys_process where pro_type = #{proType}  

not in

(

select temp.id from(select id  from sys_process where pro_type = #{protype} order by created_datetime desc limit 1) temp

)

最终结果就是正确的。结果如下：

pro_id , pro_name , pro_type , created_datetime , creator , modified_datetime , modifier

5        cc        3        2023-11-05 10:00:00  张三        null        null

6        cc        3        2023-11-06 10:00:00  张三        null        null

再做一下排序

①和②结合

select * from sys_process where pro_type = #{proType}  

not in

(

select temp.id from(select id  from sys_process where pro_type = #{protype} order by created_datetime desc limit 1) temp

)

order by created_datetime desc ;

总结：

mysql的not in函数存放的是一个子查询结果集，有limit会出现问题，必须再外层再包一层或者换成<> 或者 !=