常用SQL语句

一、update数据

UPDATE t_h5activity_attend_bulletking t,
    (SELECT 
        bulletking_id, member_id, COUNT(1) new_like_count, 1 AS week
    FROM
        bulletking_like
    WHERE
        modify_time >= '2021-12-22 00:00:00'
        AND modify_time <= '2021-12-26 23:59:59'
        AND be_like = 'true'
    GROUP BY bulletking_id , member_id) t2 
SET 
    t.liked_count = t.liked_count + t2.new_like_count
WHERE
    t.bulletking_id = t2.bulletking_id
        AND t.member_id = t2.member_id
        AND t.week = t2.week;

二、替换字符

-- 去<at>
update t_mem_member_note a,
(
    select 
        id, 
        replace(replace(note,'<at>' ,''),'<\/at>','') note_new 
    from 
        t_mem_member_note 
    where
        deleted = 0
        and (note is not null and note !='' )
) b
set a.note = b.note_new
where a.id = b.id;

三、去空格

-- 去 空格
update t_mem_member_note a,
(
    select 
        id, 
        replace(note,' ','') note_new
    from 
        t_mem_member_note 
    where
        deleted = 0
        and (note is not null and note !='' )
) b
set a.note = b.note_new
where a.id = b.id;

四、locate

关联表有多条数据，用locate实现一条一条更新(多次执行update语句)

UPDATE t_mem_member_note a,
    (SELECT 
        t.id,
            t2.member_nick_name,
            t.note,
            REPLACE(t.note, t2.member_nick_name, '') note_new
    FROM
        t_mem_member_note t
    LEFT JOIN t_mem_member_note_at t2 ON t.id = t2.note_id
    WHERE
        t.deleted = 0 AND t2.deleted = 0
            AND (t.note IS NOT NULL AND t.note != '')
            AND t2.member_nick_name IS NOT NULL
            AND LOCATE(t2.member_nick_name, t.note)
    ORDER BY id) b 
SET 
    a.note = b.note_new
WHERE
    a.id = b.id;

五、删除重复数据

delete  from t_mem_member_school  
  where id not in(
    select t.id from (
       select max(id) id from t_mem_member_school  group by member_id,school_id having max(id)
    ) t 
);