有关mysql数据库查询

 

1. 在同一个表中，change_date在同一天lock和unclock，并且unlock（解锁）之前已经lock（锁定），还要避免多个unlock对应同一个lock，GROUP BY可以去掉此字段的重复部分

SELECT * from (
	select * from (
	  	select c1.*,c2.id stopcard  from card_status_change_log c1
		INNER JOIN card_status_change_log c2 on TO_DAYS(from_unixtime(c1.change_date)) = TO_DAYS(from_unixtime(c2.change_date)) and c2.action='stop card' and c1.id>c2.id  and c1.card_no = c2.card_no
		WHERE c1.action='Unlock card' or c1.action='Unlock card!'  ORDER BY c2.id DESC)t
	GROUP BY id
) tt 
GROUP BY stopcard