【MySQL】好用的SQL语句

项目中用到的好的SQL记录，持续更新中

1、根据某个字段查询数据库重复的数据

SELECT * FROM t_agriguarantee_cust_copy WHERE CUST_CODE IN (
    -- 查找重复的客户编码
    SELECT CUST_CODE FROM t_agriguarantee_cust_copy GROUP BY CUST_CODE HAVING COUNT(CUST_CODE) > 1
);

2、删除重复数据，保留一条

这个语句，要记住一点，就是子查询要再包上一层查询，然后起个别名

-- 删除
DELETE FROM t_agriguarantee_order WHERE orderId IN ( 
SELECT * from (
SELECT min(orderId) FROM t_agriguarantee_order WHERE ORDER_NO IN (
    -- 查找重复的客户编码
    SELECT ORDER_NO FROM t_agriguarantee_order GROUP BY ORDER_NO HAVING COUNT(ORDER_NO) > 1
)) as a)

3、查询重复数据中最新的一条记录

select * from t_agriguarantee_overdue_loan_info a where not EXISTS
   (select 1 from t_agriguarantee_overdue_loan_info where LOAN_RCT_NO=a.LOAN_RCT_NO and
        LOAN_CREATE_TIME>a.LOAN_CREATE_TIME)