mysql

删除重复数据

delete from gzfz_special_account WHERE id IN (SELECT
id
FROM
gzfz_special_account
GROUP BY
id
HAVING COUNT(id) > 1);

左连接去重

SELECT
*
FROM
gzfz_special_account u
LEFT JOIN (
select r.acc_id,r.create_date from gzfz_special_account_status r 
group by r.acc_id,r.create_date
ORDER BY
r.create_date
DESC
limit 1
) tmp_r ON u.id = tmp_r.acc_id


SELECT
*
FROM
gzfz_special_account a
LEFT JOIN (
SELECT gsas.* FROM gzfz_special_account_status gsas
LEFT JOIN (
SELECT MAX(create_date) AS create_date, acc_id FROM gzfz_special_account_status GROUP BY acc_id
) tmp ON tmp.acc_id = gsas.acc_id
WHERE tmp.create_date = gsas.create_date
) tmp_r ON tmp_r.acc_id = a.id;

批量更新

UPDATE a
INNER JOIN ( SELECT yy FROM b ) c ON a.id = c.id 
SET a.xx = c.yy

将一个表更新到另外一个表

update gzfz_payment_income_record gpir set gpir.SPECIAL_ACCOUNT=gsa.SPECIAL_ACCOUNT
from gzfz_payment_income_record gpir,gzfz_special_account gsa where gpir.acc_id = gsa.id;