MySQL查询记录

MySQL查询记录

查寻某一字段的数字大于X

SELECT DISTINCT `name` FROM grade WHERE `name` NOT IN (SELECT `name` FROM grade WHERE `fenshu` <= 80)

SELECT `name` FROM grade GROUP BY `name` HAVING MIN(fenshu) > 80

查寻某一字段重复次数

SELECT `name` FROM grade GROUP BY `name` HAVING COUNT(name)  >= 3

查询某天的数据

SELECT * FROM person WHERE DATE_FORMAT(create_date,'%Y-%m-%d') = "2020-11-03"

查询连续3次都相同的数据

select distinct(l1.gender) as ConsecutiveNums  
from person l1,person l2,person l3
where 
l1.id=l2.id-1 and l1.id=l3.id-2 
and l1.gender=l2.gender and l1.gender =l3.gender

删除除id不同其它都相同的数据

delete from person where id not in(
select * from (select min(id) from person group by `name`,`gender`, `age`) tep
)