您现在的位置是：首页 >其他 >一条神奇的sql网站首页其他

背景：人脸闸机，每刷一次人脸，就会有一条记录插入到通行记录表。而闸机可能会多次识别同一个人的人脸，那么这时通行记录表就会插入多次同一个人的记录，同一个人的记录中，只不过通行时间不同而已

需求：查询出最新的5个人的通行记录信息（通行记录表100w条数据），mysql语法编写

你会怎么写这条sql？

原sql

SELECT
	a.*
FROM
	ykt_ryface_records a
INNER JOIN ( 
        SELECT 
        max( record_time ) AS record_time
        , person_id
        , max( data_id ) id 
        FROM ykt_ryface_records 
        GROUP BY person_id 
) AS c ON a.data_id = c.id
ORDER BY record_Time DESC 
LIMIT 0,5

ykt_ryface_records 通行记录表

        data_id 主键

        person_id 刷脸人的id

        record_time 通行时间

这条sql的意思是：子查询中根据刷脸人id分组，获取最大通行时间，最大的主键id 作为一个c表，然后再匹配通行记录表a，这么写会导致全表扫描，肯定慢的，执行了几十秒才有结果

本想着在c表中加一个 limit 500，应该也可以了，当如果4个人刷了500条数据呢，那么会导致最终查询出来的数据不准确。虽然现实中这个需求不可能出现。毕竟刷脸刷几次不行，他早走别的通道去了。

后来想着在结合java代码程序执行，就是执行查500条数据的sql，返回结果集，判断结果集是否有5条数据，如果没有再查500条，一直到满足5条数据为止。这样是可行的，但感觉不爽，我喜欢一条sql搞定一切

优化的sql，本来没写出来，去趟洗手间回来就写出来了，哈哈

SELECT
	a.* 
FROM
	ykt_ryface_records a
	INNER JOIN ( 
	
				SELECT a.person_id,a.person_name,@idStr,@count,@dataid id
				from ykt_ryface_records a
				INNER JOIN (select @idStr:=0,@count:=0,@dataid:=0) b on 
				(
				IF(find_in_set(IFNULL(a.person_id,-999),@idStr)=0
				,concat(@idStr:=CONCAT(@idStr,',',IFNULL(a.person_id,-999)),@count:=(LENGTH(@idStr) - LENGTH(REPLACE(@idStr,',',''))),@dataid:=a.data_id)
				,-1) = 0 
				)  
				order by a.record_Time DESC  
				LIMIT 5

) AS c ON a.data_id = c.id 
ORDER BY record_Time DESC

精华都在c表中的inner join，以前我都是在select 列名这用临时变量，灵机一动在inner join 的on条件后也可以用。c表的sql大概意思是，降序查询每条记录，然后对每条记录的的刷脸人id，主键id，放到临时变量中。查询逻辑如：先查第一条记录，记录了相关id，这时候的条件是on 0=0 然后查第二条，发现和第一条记录的相关id一样，就返回-1，那么条件就是 on -1 = 0，因为是内连接，所以第二条记录就没有了。按照这样的逻辑一次类推，巧妙使用limit 5就找出5个人的最新数据了。然后再作为c表关联a表查询，就避免了全表扫描了。该sql执行时间在0.1秒以下

感想：

巧妙利用临时变量，解决很多复杂的sql查询，提高效率。