查询表中重复数据，按部门排序

DROP TABLE IF EXISTS `tempTb`;
CREATE TEMPORARY TABLE tempTb SELECT product_id,project_id,COUNT(*) AS num FROM product GROUP BY product_id, project_id HAVING num > 1 ORDER BY project_id;
DROP TABLE IF EXISTS MaxTb;
CREATE TEMPORARY TABLE MaxTb SELECT product_id,project_id,MAX(product_id) AS maxPdt FROM product
SELECT MAX(product_id),project_id FROM product 
SELECT product.`product_id`,product.`activity_id`,product.`project_id` FROM tempTb LEFT JOIN product ON product.`product_id` = tempTb.product_id AND product.`project_id` = tempTb.project_id;



DROP TABLE IF EXISTS maxTb;
CREATE TEMPORARY TABLE maxTb SELECT product_id,project_id,MAX(product_id) AS maxPdt FROM product GROUP BY project_id;
SELECT maxPdt FROM maxTb;


SELECT a.product_id,a.project_id,a.activity_id,COUNT(*) AS 序号
FROM product AS a ,product AS b
WHERE a.project_id=b.project_id AND   a.product_id<=b.product_id  AND a.`activity_id` >= b.`activity_id`
GROUP BY a.project_id,a.`activity_id`


DROP TABLE IF EXISTS product_temp;
CREATE TEMPORARY TABLE product_temp 
SELECT project_id,activity_id,product_id,count(*) AS num FROM product GROUP BY product_id,project_id HAVING num > 1 ORDER BY project_id,activity_id;

SELECT product.product_id,product.activity_id,product.project_id from product_temp 
LEFT JOIN product ON product_temp.project_id = product.project_id AND product_temp.product_id = product.product_id;

-- 进行过滤查询,查询多余的去掉最小activity_id那条记录
select product_id,activity_id,project_id from product a
where (a.product_id,a.project_id) in (select product_id,project_id from product group by product_id,project_id having count(*) > 1)
and (product_id,project_id,activity_id) not in (select product_id,project_id,min(activity_id) from product group by product_id,project_id having count(*)>1)

-- 排序操作
set @a1='';
set @b1=1;
select project_id,activity_id,
       if(@a1='',@b1,if(@a1=project_id,@b1:=@b1+1,@b1:=1)),
       @a1:=project_id 
  from product 
  order by project_id,activity_id desc;
  
 --序号
set @a1='';
set @b1=1;
select project_id,activity_id,product_id,
       if(@a1='',@b1,if(@a1=project_id,@b1:=@b1+1,@b1:=1)) AS nob,
       @a1:=project_id AS prj_id,
				(select MAX(product_id) from product GROUP BY @al)
  from product 
  order by project_id,activity_id desc; 
  
  -- 过滤掉重复的数据
  set @a1='';
set @b1=1;
select product_id,activity_id,project_id,
       if(@a1='',@b1,if(@a1=project_id,@b1:=@b1+1,@b1:=1)) AS nob,
       @a1:=project_id AS prj_id
from 
(
select product_id,activity_id,project_id from product a
where (a.product_id,a.project_id) in (select product_id,project_id from product group by product_id,project_id having count(*) > 1)
and (product_id,project_id,activity_id) not in (select product_id,project_id,min(activity_id) from product group by product_id,project_id having count(*)>1)
) AS product_ta
order by project_id,activity_id desc;

-- 创建临时表，更新数据库
DROP TABLE IF EXISTS product1_temp;
CREATE TABLE product1_temp SELECT * FROM product1;
UPDATE product1 SET product1.product_id = 
(SELECT product1_temp.product_id-product1_temp.project_id FROM product1_temp 
WHERE product1.project_id = product1_temp.project_id
AND product1.activity_id = product1_temp.activity_id);  

--将最大值融合进去
set @b1=1;
select product_id,activity_id,project_id,maxPrd,
       if(@a1='',@b1,if(@a1=project_id,@b1:=@b1+1,@b1:=1)) AS nob,
       @a1:=project_id AS prj_id
from 
(
select product_id,activity_id,a.project_id,maxPrdTb.maxPrd
from product AS a 
LEFT JOIN (SELECT project_id,MAX(product_id) AS maxPrd FROM product GROUP BY project_id) AS maxPrdTb
ON a.project_id = maxPrdTb.project_id
where (a.product_id,a.project_id) in (select product.product_id,product.project_id from product group by product.product_id,product.project_id having count(*) > 1)
and (a.product_id,a.project_id,a.activity_id) not in (select product.product_id,product.project_id,min(product.activity_id) from product group by product.product_id,product.project_id having count(*)>1)
) AS product_ta
order by project_id,activity_id ASC;

set @a1='';
set @b1=1;
select product_id,activity_id,project_id,maxPrd,
       if(@a1='',@b1,if(@a1=project_id,@b1:=@b1+1,@b1:=1)) + maxPrd AS newPrdId,
       @a1:=project_id AS prj_id
from 
(
select product_id,activity_id,a.project_id,maxPrdTb.maxPrd
from product AS a 
LEFT JOIN (SELECT project_id,MAX(product_id) AS maxPrd FROM product GROUP BY project_id) AS maxPrdTb
ON a.project_id = maxPrdTb.project_id
where (a.product_id,a.project_id) in (select product.product_id,product.project_id from product group by product.product_id,product.project_id having count(*) > 1)
and (a.product_id,a.project_id,a.activity_id) not in (select product.product_id,product.project_id,min(product.activity_id) from product group by product.product_id,product.project_id having count(*)>1)
) AS product_ta
order by project_id,activity_id ASC;
  
  

 进行整合查询

set @a1='';
set @b1=1;
DROP TABLE IF EXISTS my_product;
CREATE TEMPORARY TABLE my_product
select product_id,activity_id,project_id,maxPrd,
       if(@a1='',@b1,if(@a1=project_id,@b1:=@b1+1,@b1:=1)) + maxPrd AS newPrdId,
       @a1:=project_id AS prj_id
from 
(
select product_id,activity_id,a.project_id,maxPrdTb.maxPrd
from product AS a 
LEFT JOIN (SELECT project_id,MAX(product_id) AS maxPrd FROM product GROUP BY project_id) AS maxPrdTb
ON a.project_id = maxPrdTb.project_id
where (a.product_id,a.project_id) in (select product.product_id,product.project_id from product group by product.product_id,product.project_id having count(*) > 1)
and (a.product_id,a.project_id,a.activity_id) not in (select product.product_id,product.project_id,min(product.activity_id) from product group by product.product_id,product.project_id having count(*)>1)
) AS product_ta
order by project_id,activity_id ASC;
SELECT * FROM my_product;

 

以下是数据库的图片

#mysql中两张表数据的部分更新，用tb2的部分数据更新tb1
UPDATE tb1,tb2 SET tb1.address=tb2.address WHERE tb1.name=tb2.name

 
 exists去重

SELECT
*
FROM
表 main
WHERE
NOT EXISTS (
SELECT * FROM 表 sub
WHERE main.division = sub.division AND main.district > sub.district
)