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