SQL：数据去重，保留一条

超级SQL的开始

## 查询重复的数据
SELECT
        m.ma_code,
        count(1)
FROM
        bi_matierals m
GROUP BY
        m.ma_code
HAVING
        count(1) >1

## 查看重复数据 id不等于 最大的那个
SELECT *
FROM bi_matierals ta
WHERE
        ta.id<>(
                SELECT
                        t.maxid
                FROM
                        (SELECT max(id) as maxid  from  bi_matierals as tb where ta.ma_code = tb.ma_code) t
        
        )

重要结论

select方法 返回的是一个 table， 所以取了别名，才可以拿来用，才可以from t

应用实例1

# 202种标准件 在 非标准件里面有 重复记录， 查出这些重复数据
SELECT a.id, a.ma_code, a.ma_name, a.ma_specification, a.is_norm, a.`status` FROM bi_matierals AS a
JOIN bi_matierals AS b
ON a.ma_specification=b.ma_specification
WHERE a.is_norm = '0' and b.is_norm = '1'

# 逻辑删除上面 找出的数据（join查出来的是子表，我们只能更新主表）
UPDATE bi_matierals c
join (SELECT a.id, a.ma_code, a.ma_name, a.ma_specification, a.is_norm, a.`status` FROM bi_matierals AS a
	JOIN bi_matierals AS b
	ON a.ma_specification=b.ma_specification
	WHERE a.is_norm = '0' and b.is_norm = '1') d
ON c.id = d.id
set c.`Status` = 2