mysql 查询重复数据同一个工号有多条数据和 date_add 使用方法

偏执的太偏执、 2022-10-22 02:47 12阅读 0赞

1 根据某个字段查询重复数据 id大的单独查询出来

SELECT
    	* 
    FROM
    	zxpx.t_app_usermlq 
    WHERE
    	job_no IN ( SELECT job_no FROM zxpx.t_app_usermlq GROUP BY job_no HAVING count( job_no ) > 1 ) 
    	AND id NOT IN ( SELECT min( id ) FROM zxpx.t_app_usermlq GROUP BY job_no HAVING count( job_no )> 1 );

2 根据某个字段查询重复数据 id小的单独查询出来

SELECT
    	* 
    FROM
    	zxpx.t_app_usermlq 
    WHERE
    	job_no IN ( SELECT job_no FROM zxpx.t_app_usermlq GROUP BY job_no HAVING count( job_no ) > 1 ) 
    	AND id NOT IN ( SELECT max( id ) FROM zxpx.t_app_usermlq GROUP BY job_no HAVING count( job_no )> 1 );

查询people_no people_name重复的记录，排除最小id
    
    select * from people a
    
    where (a.people_no,a.people_name) in (select people_no,people_name from people GROUP BY people_no,people_name HAVING count(*)>1) and
    
    a.id not in (select min(id) from people GROUP BY people_no,people_name HAVING count(*)>1);

查询people_no people_name重复的记录，排除最大id
    
    select * from people a
    
    where (a.people_no,a.people_name) in (select people_no,people_name from people GROUP BY people_no,people_name HAVING count(*)>1) and
    
    a.id not in (select max(id) from people GROUP BY people_no,people_name HAVING count(*)>1);

5 sql 里面if使用，date\_add使用

SELECT
     tsd7.`name` 组织1,
     tsd6.`name` 组织2,
     tsd5.`name` 组织3,
     tsd4.`name` 组织4,
     tsd3.`name` 组织5,
     tsd2.`name` 组织6,
     tsd1.`name` 组织7,
      case tau.staff_type when 1 then '内勤' when 2 then '外勤' end AS 人员类型,
     tau.`name` 姓名 ,
     tau.job_no 工号,
      case rcu.type when 0 then '课程' when 2 then '培训' when 3 then '闯关' end AS 浏览内容类型,
      tac.`name` 浏览内容,
     rcu.starting_time 开始浏览时间,
     date_add(rcu.starting_time,interval if( rcu.duration >=  tacw.duration_time,tacw.duration_time, rcu.duration)  minute) 浏览结束时间,
     if( rcu.duration >=  tacw.duration_time,tacw.duration_time,rcu.duration) 学习时长
    -- tacw.duration_time 课件时长
    FROM
     zxpx.r_courseware_user rcu
     LEFT JOIN zxpx.t_app_courseware tacw ON tacw.id = rcu.courseware_id
     LEFT JOIN zxpx.t_app_usermlq tau ON rcu.user_id = tau.id 
     LEFT JOIN zxpx.t_app_course tac ON rcu.course_id = tac.id
     LEFT JOIN zxpx.t_sys_dept tsd1 ON tau.dept_id = tsd1.dept_id
     LEFT JOIN zxpx.t_sys_dept tsd2 ON tsd1.parent_id = tsd2.dept_id
     LEFT JOIN zxpx.t_sys_dept tsd3 ON tsd2.parent_id = tsd3.dept_id
     LEFT JOIN zxpx.t_sys_dept tsd4 ON tsd3.parent_id = tsd4.dept_id
     LEFT JOIN zxpx.t_sys_dept tsd5 ON tsd4.parent_id = tsd5.dept_id
     LEFT JOIN zxpx.t_sys_dept tsd6 ON tsd5.parent_id = tsd6.dept_id
     LEFT JOIN zxpx.t_sys_dept tsd7 ON tsd6.parent_id = tsd7.dept_id
     WHERE rcu.type = 0 
     AND rcu.starting_time >= '2021-03-01 00:00:00'
     AND tau.`name` != '游客' order by 学习时长 desc limit 100000 ;