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 ;

 

获取上个月

SELECT
b.xalljgmc,
b.xjgmc,
substr(a.sys_xzqh, 1, 6) AS sys_xzqh
FROM
sr_main a,
mz_xzjg b
WHERE
a.sys_xzqh = b.node_id
AND mjzjsrq IS NOT NULL

AND mjzjsrq <= CONVERT (
date_format(
(now() - INTERVAL 1 MONTH),
'%Y-%m-%d'
) USING gbk
)

补充：

select date_add('2018-06-26',INTERVAL '5' day);-- 2018-07-01
select curdate()；-- 2020-06-10
select date_add(curdate(), INTERVAL 3 MONTH) -- 2020-09-10
select DATE_FORMAT(
date_add(curdate(), INTERVAL 3 MONTH),
'%Y-%m-%d'
) -- 2020-09-10；

 

UPDATE sr_main
SET mjzjsrq = CASE
WHEN mdblx = '2' THEN
DATE_FORMAT(
date_add(curdate(), INTERVAL 3 MONTH),
'%Y-%m-%d'
)
ELSE
DATE_FORMAT(
date_add(curdate(), INTERVAL 1 YEAR),
'%Y-%m-%d'
)
END
WHERE
pk_sr_main = '1000028382434168832';

 

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;