mysql怎么删除临时表里的数据_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