mysql操作：一个表a字段更新另一个表b字段；表多字段去重

淡淡的烟草味﹌ 2023-10-09 21:11 34阅读 0赞

1、全表同步操作：使用一个表的字段t1.a更新表的另一个表的字段t2.b，整个表t1.a字段全部替换，关联条件 t1.c=t2.c

UPDATE
      user_record t1,
      `user` t2
    SET
      t1.a = t2.b
    WHERE t1.c = t2.c;

2、归总操作：一个表的全部内容根据user\_id和type去重，选最新记录，归总到到另一个表里

INSERT INTO `user_choose` (user_id, TYPE, username, phone)
    (SELECT
      user_id,
      TYPE,
      username,
      phone
    FROM
      user_choose_log
    WHERE id IN
      (SELECT
        MAX(id)
      FROM
        user_record
      WHERE user_id > 0
      GROUP BY user_id,
        TYPE)
    );

3、同一个表中，变更表字段顺序：username、phone字段都放到id字段之后

ALTER TABLE `user`
      MODIFY `username` VARCHAR (63) DEFAULT NULL AFTER `id`,
      MODIFY `phone` VARCHAR (63) DEFAULT NULL AFTER `id`;

4、b库je\_log与a库rt\_rights原本有关联，但开发未记录关联id，所以我们用时间关联：上下一分钟内创建、关联两个表数据，查询出结果后，把right.id记录进je\_log表，使两个表直接关联起来

SELECT rights.id AS rights_id,rights.create_time,t.je_id
    FROM a.rt_rights AS rights
    LEFT JOIN 
    (
    SELECT id as je_id,user_id,create_time,DATE_ADD(create_time, INTERVAL -1 MINUTE) AS start_t,DATE_ADD(create_time, INTERVAL 1 MINUTE) AS end_t
    FROM b.je_log
    ) AS t
    ON rights.user_id=t.user_id
    WHERE rights.create_time >t.start_t AND rights.create_time <t.end_t;

5 跟新表，指定记录里，创建时间加1天 (若是减一天，INTERVAL -1即可)

UPDATE sign SET create_time = DATE_ADD(create_time,INTERVAL 1 DAY) WHERE id=15;