mysql日期函数及重复数据的查询

-- 日期函数
select CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,year(CURRENT_DATE),month(CURRENT_DATE);
select * from sr_main where TIMESTAMPDIFF(DAY,SYS_CREATETIME,SYSDATE())>7 and SYS_SPZT <>1 ;

1.目标查出表中重复的身份证
-- 以下为错误写法因为 where 后面不能用聚合函数
select * from sr_main where mhzsfz in (
select mhzsfz from sr_main where count(MHZSFZ)>1
)
-- 正确写法
select * from sr_main where mhzsfz in (
select mhzsfz from sr_main GROUP BY mhzsfz having count(mhzsfz)>1
)

1.目标查出表中重复的身份证且数据为2019年，单据类型为db_jz，时间为2019年
select * from sr_main a where mhzsfz in (
select mhzsfz from sr_main GROUP BY mhzsfz having count(mhzsfz)>1
) and SYS_CREATETIME like concat(‘2019‘,‘%‘) and INSTR(‘db_jz‘,a.mdjlx)>1;