mysql 临时表 删除一个表中的重复数据

mysql 临时表：

 

 

-- 用MaterialSortID和  MatClfID关联可以获得物料分类中文
SELECT a.id,a.material_category,a.material_name,t.material_sort_name from scm_material_base a left JOIN scm_material_type t on a.material_category=t.material_sort_id

update scm_material_base a,scm_material_type b set a.material_category_name=b.material_sort_name where a.material_category=b.material_sort_id
-- 计量单位表  通过  UnitID字段 关联
SELECT * from scm_material_unit

SELECT a.id,a.material_category,a.material_name,t.unit_name from scm_material_base a left JOIN scm_material_unit t on a.measure_unit=t.unit_id

update scm_material_base a,scm_material_type b set a.material_category_name=b.material_sort_name where a.material_category=b.material_sort_id


update scm_material_base a,scm_material_unit b set a.measure_unit_name=b.unit_name where a.measure_unit=b.unit_id

-- 先去重

SELECT min(id), material_code,count(1) from scm_material_base group by material_code HAVING count(1)>1


SELECT * from scm_material_base where material_code ='0100000000290'


SELECT DISTINCT material_code from scm_material_base

DELETE FROM scm_material_base  where id not in(
SELECT min(id) from scm_material_base group by material_code )

CREATE TEMPORARY TABLE tmp_table(
    SELECT min(id) from scm_material_base group by material_code
);

CREATE TEMPORARY TABLE tmp_table(
    SELECT min(id) from scm_material_base group by material_code
);

DELETE FROM scm_material_base  where id not in(
select * from tmp_table
)


select count(1) from scm_material_base