去重

DELETE 
    FROM sys_job_newstaff
WHERE id NOT IN (
	SELECT temp.min_id FROM (
		SELECT 
		    MIN(id) min_id 
		FROM 
		    sys_job_newstaff 
		where 
		    card_id is not null 
		GROUP BY 
		    card_id
	)AS temp
) and card_id is not null;

查重复

select id,title,status from sys_spider_news where title in (select title from sys_spider_news group by title having count(title) > 1) order by title;

取交集

select company_id from 
(
select company_id from sys_company_tags_relations r where r.del_flag = 0 and r.is_initiative = 1 and r.tag_id in ('598b5449ecc5a50323f58494fb4a1e22')
union all
select company_id from sys_company_tags_relations r where r.del_flag = 0 and r.is_initiative = 0 and r.tag_id in ('320e4d2087e87e0de75a45ba7f213a0d')
) t GROUP BY company_id HAVING count(company_id) = 2

查询单表的数据是否重复

SELECT
 D .sbnf,
 A .*, CASE
WHEN b.num > 1 THEN
 'true'
ELSE
 'false'
END isRepeat,
 CASE
WHEN A .sbr = '764' THEN
 'true'
ELSE
 'false'
END hasToDo
FROM
 MA_KJCG_LW_TD A
LEFT JOIN (
 SELECT
  lwtm,
  COUNT (*) num
 FROM
  MA_KJCG_LW_TD
 GROUP BY
  lwtm
) b ON A .lwtm = b.lwtm
LEFT JOIN ma_kjcg_jlsq_td D ON A . ID = D .fk_sbtj;

逗号分隔的数据转单条

t.work_type的数据类似为A01A01,A01A02

SELECT 
	t.ID, 
	substring_index(substring_index(t.work_type, ',', b.help_topic_id + 1), ',', -1) AS work_type_code
FROM 
	bus_task t
JOIN 
	mysql.help_topic b ON b.help_topic_id < length(t.work_type) - length(REPLACE(t.work_type, ',', '')) + 1

分组查最大最小

select max(result + 0) FROM (
	SELECT
		sum(result) as result
	FROM
		bus_vote_result
	WHERE
		be_assess_person = #{objId}
		AND ticket_id = #{ticketId}
		AND instance_id = #{instanceId}
	GROUP BY assess_person
) maxResult

综合考评区分不同人和指标票种进行统计

SELECT
	*,ROUND(total * assess_weight / 100 / person_num, 3) as score
FROM
	(
	SELECT
		sum( result.result ) AS total,
		norm.NAME AS norm_name,
		USER.realname,
		ticket.NAME,
		count( result.be_assess_person ) AS person_num,
		weight.assess_weight 
	FROM
		bus_vote_result AS result
		LEFT JOIN bus_runtime_assess_ticket_type AS ticket ON result.ticket_id = ticket.id
		LEFT JOIN sys_user AS USER ON result.be_assess_person = USER.id
		LEFT JOIN bus_runtime_assess_norm AS norm ON result.norm_id = norm.id
		LEFT JOIN bus_runtime_vote_weight AS weight ON result.ticket_id = weight.assess_type 
	WHERE
		result.instance_id = '1613081295000248321'
	GROUP BY
		norm.NAME,
		USER.realname,
		ticket.NAME,
	weight.assess_weight 
	) t

To Be Continued！😎